YTD Set Analysis

Wed, 04 Jan 2012 12:44:07 GMT

I have the following expression for my YTD calculation in a straight table -

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month=,Drawer={'4'}>}JrnAmt). The db has records from 1 July 2011 onwards so this expression works for YTD up to Dec 2011.

The problem with this is that when the 'Jan 2012' is selected, the total for YTD is only for 'Jan 2012' instead of 'Jul 2011' to 'Jan 2012' (Fiscal year is 'Jul' to 'Jun'). So if 'Jan' is selected the YTD sum should be for 'Jul 2011' to 'Jan 2012'.

Is there a way to change this set analysis so that the desired result is achieved without having to build a calender in the script?

Regards

Danie

YTD Set Analysis

lironbaram — Wed, 04 Jan 2012 13:34:15 GMT

hei

you can set a varaible with this formula

vMinDateYTD=if(num(month(vMaxDate))<=6,makedate(max(Year)-1,7,1),makedate(max(Year),7,1));

then your set analysis will be

sum({<Year,Date={'>=$(vMinDateYTD) <=$(vMaxDate)'},Month=,Drawer={'4'}>}JrnAmt).

topic YTD Set Analysis in QlikView

YTD Set Analysis

YTD Set Analysis