Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
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';
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