topic Re: SET ANALYSIS ? YTD? in QlikView

SET ANALYSIS ? YTD?

Tue, 05 Aug 2014 19:23:19 GMT

Hi,

This is what giving me a hard time. This is YTD sales.

When the user selects only Q1, then it should display the YTD sales of the current year(2014).

When the user select Q2, then it should display the YTD sales for 2013 from Jan-Jun because this year we are still in Q1.

Similarly when we select only a month, e.g. Mar, then it should display the YTD sales for 2014 Jan-Mar.

But when you select only Aug, it should display the YTD for 2013 Jan-Aug.

When the user selects year 2013 and Feb then it should display the YTD Sales for 2013 Jan-Feb.

Thank you.

Re: SET ANALYSIS ? YTD?

JonnyPoole — Tue, 05 Aug 2014 20:29:45 GMT

There a few ways to do this one.

I added a new field called monthnumber to your data model and then i created 2 variables ... vCurrentYear and vCurrentMonthNumber. They are both the max of their respective fields in the current selection context.

the YTD sales (dependent on users time selection) uses currentyear and <=currentmonthnumber to give you YTD.

=Sum( {$<Year={$(vCurrentYear)},MonthNumber={'<=$(vCurrentMonthNumber)'} >} Sales)

Re: SET ANALYSIS ? YTD?

Wed, 06 Aug 2014 06:49:41 GMT

can u share qvw

Re: Re: SET ANALYSIS ? YTD?

JonnyPoole — Wed, 06 Aug 2014 13:45:14 GMT

here is the QVW