1 Reply Latest reply: Dec 21, 2011 11:31 AM by Stefan Wühl RSS

    Calculate YTD

      Hi

       

      I have a variable($Total) that works fine. Now I want to calculate YTD.

       

      Report O/p:

      Week     Weekly Total     YTD Total

      1            1000                  10000

       

      Variable --> $(Total) = sum(A * B * C) / 100

       

      Weekly Total = $(Total)

       

      I am unable to calculate YTD values for the variable now.

        • Calculate YTD
          Stefan Wühl

          In what context do you use the variable (e.g. in a straight table with dimensions Week and (which?) expressions)?

           

          You could try using a set expression to get the YTD, maybe like

          =sum({<Week = {"<=$(=week(today()))"}>} total A*B*C) / 100

           

          To help you more, you probably need to give us some more information, in which context you want to use the YTD value and what your (date) fields are (e.g. in my above sample, if you have also a Date field and you select on it, the above expression will not work without clearing the selection like

           

          =sum({<Week = {"<=$(=week(today()))"}, Date= >} total A*B*C) / 100

           

           

           

          Alternatively, you could flag your YTD Dates in the script, maybe using

           

          LOAD

          ...

          -1* InYearToDate (Date, Today(1) ) as IsYTD,

          Date,

          ...

          from ...;

           

          Then use

          =sum( total A*B*C*IsYTD) / 100

           

           

          as YTD expression.

           

          Hope this helps,

          Stefan