12 month period - using sql
Hello everybody,
I'm currently looking for a way to create a new column (by using sql) that sends me back the total amount of the last 12 months, for a specific product and a specific month.
My file looks like the one below :
Date | Name of the product | Sales |
and I would like to create a new column wich is an amount of the last 12 months sales.
Date | Name of the product | Sales | Sales_last 12 months |
Does anyone have any ideas to do this ?
Thank you in advance for your help !
-
I think you can try something like I have shown below.
I believe you're trying to get total sales over all time and sales in the last 12 months, right?If not and you're just trying to return the entire table, you can skip the grouping step and the first sum, otherwise this will give you what you need.
===============================================================
-
Hello Adam,
Thank you for your reply ! I tried the formula you gave me.
However, I just replaced "currentdate" with another formula including "n.[date]" since i expect a result for each n.date month and not only for the last 12 months until "currentdate"..
But the result from the new column isn't correct.. I think it's because of the hilighted item below :
This is why, I also tried different things :
- WHERE n.Date BETWEEN addmonths(o.Date,-12) AND addmonths(o.Date,0)
- WHERE n.Date > addmonths(n.Date,-12) AND n.Date <= addmonths(n.Date,0)
But it doesn't work... it seems that Sisense does not understand that it is 12 month from this year and previous year.
Thank you in advanced for your reply !
-
Marco R, I would probably have to see the data to understand why it isn't working.
If you have multiple dates for products in your fact_douanes table you will end up with results that are much larger than you expect. That is because for each record it would be calculating the TTM sum, but then you're summing up that value - the sum of all of those sums.
Since you're trying to get a TTM sum to associate with each product, you'll need to pick a point in time - that's why CURRENTDATE works.
I would recommend you create a separate table with only the subquery logic and run that to understand what that table looks like - then integrate that logic into your main query within the subquery section.
Please sign in to leave a comment.
Comments
4 comments