topic YTD /Current Previous Year Data Display in QlikView

YTD /Current Previous Year Data Display

Tue, 21 Apr 2009 15:22:35 GMT

Dear All,

I wanted to show pivot table

User Selects Any one year from Transaction date and based o that selection

my pivot table should show me below output

ItemName PreviousYearSale Value CurrentYear Sale Value

X 10 5

Y 20 10

Z 30 18

Thanks in advance

Can anyone halp me!!!!!!!!!!!!!

YTD /Current Previous Year Data Display

Tue, 21 Apr 2009 15:52:40 GMT

Hi,

Which version of QlikView are you using? You can use the SetAnalysis functionality but this is only available from QV 8.5> (at least, in an easy to apply way).. The Annual Comparison sheet in the "What's New in QV 8.5" application (located in your local Program Files/QlikView/Examples/Documents shows exactly how to do this..

Regards, Sander

YTD /Current Previous Year Data Display

Tue, 21 Apr 2009 16:30:45 GMT

Thats relatively easy. I suppose for the first scenario you have a linkage between year of sales and sales value

Previous Year Sales value:

sum(if(YearOfSales=(year(today())-1),SaleValue))

Current Year Sales Value:

sum(if(YearOfSales=year(today()),SaleValue))

Important:

If you do calculations with DateTime-Functions like adding or subtracting (see bold text in the first formular), you have to enclose the whole term in brackets.

Kind regards,

Rolf

YTD /Current Previous Year Data Display

Tue, 21 Apr 2009 16:39:38 GMT

Hi Rolf,

That was also my first idea but the only problem with this solution is that when the user filters on a year other than the current year (as being the year which must be compared to the previous year), for example 2008-2007 the expressions don't work anymore. That is exactly what SetAnalysis solves, using the selected year as a variable for the current year and the previous year as selected year minus 1.

In essence it does the same as your solution but the variables make the expressions dynamic.

Regards, Sander

YTD /Current Previous Year Data Display

Tue, 21 Apr 2009 16:49:38 GMT

With QV versions below 8.5 you can do the same by using num(GetFieldSelections(YearofSales)) as a replacement for the current year.

Regards,

Rolf

YTD /Current Previous Year Data Display

Tue, 21 Apr 2009 17:05:14 GMT

Hi,

By assigning the Max of the date field to the variable, i.e. Var1 = year(max(YearOfSales)) and using the same variable with 'ALL' Function in the expression will do the trick.

Previous Year Sales value:

sum(All if(YearOfSales=(Var1-1),SaleValue))

Current Year Sales Value:

sum(if(YearOfSales=(Var1),SaleValue))

Hope this will help you...

Regards

Sridhar

YTD /Current Previous Year Data Display

Tue, 21 Apr 2009 18:05:03 GMT

thanks all for ur help

the tricks did work