2 Replies Latest reply: Oct 29, 2012 4:07 AM by Roland Stadler RSS

    Calculation of real YTD value by month

    Roland Stadler

      I am trying to achieve the calculation of a YTD value. All examples I have tried before do not apply for one of the following requirements:

       

      • It must be possible to use the YTD value by month, eg. in a chart.
        I could not find a SET ANALYSIS example that achieves this.

        

      • The YTD value must always reference the whole Fiscal Year. If I select a Quarter, the individual YTD values for these three Periods must not change compared to the selection of the whole Fiscal Year (otherwise it would be QTD).
        This eliminates the Inter-Record Chart Functions (e.g. rangesum)

       

      • Equally, I cannot use the Full Aggregation or other "moving window" functionality I've stumbled upon, as the YTD-Value must restart at ZERO every Year. Even if two Fiscal Years are selected.

        

      Basically, I just want a fixed YTD value by month, that could also be generated by the Load script.

      Please see the attached example. 

       

      I am glad for any nudge in the right direction.

        • Re: Calculation of real YTD value by month
          Justin Hsu

          I changed your script for your reference.

          Hope it work for you.

           

          SET ThousandSep="'";

          SET DecimalSep='.';

          SET MoneyThousandSep="'";

          SET MoneyDecimalSep='.';

          SET MoneyFormat='Fr. #''##0.00;Fr.-#''##0.00';

          SET TimeFormat='hh:mm:ss';

          SET DateFormat='DD.MM.YYYY';

          SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

          SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

          SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

           

          exampleData_T:

          LOAD * INLINE [

              PerFY, Per, FY, ValueMTD

          2011-01,01,2011,5

          2011-02,02,2011,7

          2011-03,03,2011,4

          2011-04,04,2011,4

          2011-05,05,2011,9

          2011-06,06,2011,5

          2011-07,07,2011,3

          2011-08,08,2011,5

          2011-09,09,2011,6

          2011-10,10,2011,4

          2011-11,11,2011,5

          2011-12,12,2011,7

          2012-01,01,2012,5

          2012-02,02,2012,4

          2012-03,03,2012,7

          2012-04,04,2012,4

          2012-05,05,2012,7

          2012-06,06,2012,6

          2012-07,07,2012,7

          2012-08,08,2012,8

          2012-09,09,2012,4

          2012-10,10,2012,6

          2012-11,11,2012,7

          2012-12,12,2012,5

          ];

           

          exampleData:

          LOAD *,

               If(PerFY > Previous(PerFY), RangeSum(ValueMTD, If(FY >Previous(PerFY),0,Peek('ValueYTD'))), ValueMTD) AS ValueYTD

          RESIDENT exampleData_T

          ORDER BY PerFY;

           

          DROP TABLE exampleData_T;

           

          TAG Fields PerFY, Per, FY With '$dimension';

          TAG     Field Value With '$measure';

            • Re: Calculation of real YTD value by month
              Roland Stadler

              Hi Justin,

               

              thanks for your support and sorry that it took me so long to answer. What your are proposing is unfortunately not exactly what I need.

               

              Howerver, I created the solution in the meantime and would like to share it here.

               

              To show the real YTD values, I created a second YTD calendar, that is linked to the standard (MTD) calendar. Every Period in the YTD calendar is linked to all previous MTD Periods in the same fiscal year, therefore aggregating the monthly values correctly.

               

              Example:

              --> FYPeriodYTD "2012-01" is linked to FYPeriod "2012-01"

              --> FYPeriodYTD "2012-03" is linked to FYPeriod "2012-01", "2012-02" and "2012-03"

               

              In the chart, it is easily possible to switch from YTD to MTD by using a cyclic group.

               

              Please see the attached QVW for my solution.

               

              Kind regards,

              Roland