0 Replies Latest reply: Apr 22, 2015 6:21 AM by Jorge da Cunha RSS

    Rolling Total by Year

      Hi, I have a field that I use to calculate my Closing SOH. Basically it's all the movement in and out by day. It dates back to 2009. So whenever I want to see my closing SOH for a particular date, I need to sum all the transactions from 2009 right up to the date I've selected. The syntax I use for that is:

       

      sum({$<[Tran Date]={'<=$(vMaxDate])'}>}Quantity)

       

      Now, I want to see what was the Closing SOH at the end of week 42 in 2015, 2014 and 2013.

       

      When I add the Year and Week as dimensions, the formula breaks.

       

      I'm able to get it right if I Pivot it and create 3 different expressions:

       

      sum({$<[Fin Year]=, [Fin Week]=, [Tran Date]={'<=$(vMaxDate)'}>}Quantity)

       

      sum({$<[Fin Year]=, [Fin Week]=, [Tran Date]={'<=$(vMaxDateLY)'}>}Quantity)

       

      sum({$<[Fin Year]=, [Fin Week]=, [Tran Date]={'<=$(vMaxDateLLY)'}>}Quantity)

       

      But I need it as one expression in a straight table.

       

      Can anyone help me?