Calculation of real YTD value by month

[rolandstadler]

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.

[#2]

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';

[rolandstadler]

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