12 month period - using sql

Comments

4 comments

  • Avatar
    Adam Bender (Edited )

    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.

     

    ===============================================================

     

    0
    Comment actions Permalink
  • Avatar
    Mike Flanagan

    I'd be interested in knowing this as well

    0
    Comment actions Permalink
  • Avatar
    Marco R

    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 !

     

     

    0
    Comment actions Permalink
  • Avatar
    Adam Bender

    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.

    0
    Comment actions Permalink

Please sign in to leave a comment.