Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

rolandstadler
New Contributor II

Calculation of real YTD value by month

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 Replies
Not applicable

Re: Calculation of real YTD value by month

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
New Contributor II

Re: Calculation of real YTD value by month

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

Community Browser