topic Re: Iterative Year vs Prior Year in QlikView

Iterative Year vs Prior Year

Anonymous — Tue, 12 Aug 2014 03:52:55 GMT

I have a scenario. In the sample table below, I need to show the sales by year… And for each year, I need to show the last yr and last 2nd year sales for that year.

For example in 2014,

Current Year = 2014 Sales

Last Year = 2013 Sales

Current Year = 2013 Sales

Last Year = 2012 Sales

 |----------2013------------|---------2014-------------|

 | Last Year | Current Year | Last Year | Current Year |

Ive tried but when i nest them under a year dimension.. the calculations are not working..

is there a way around this, to come up with this kind of report format? our user is very particular in having such format..

We are using an existing model and are not allowed to do any modifications so are options are limited to report calculations, expression or set analysis..

many thanks for the help.

Re: Iterative Year vs Prior Year

ashfaq_haseeb — Tue, 12 Aug 2014 05:23:57 GMT

Hi,

Have a look at attached application.

Regards

ASHFAQ

Re: Iterative Year vs Prior Year

Tue, 12 Aug 2014 05:36:41 GMT

You can do this by using set analysis, for current year sales

sum({<Year={'$(=Max(Year))'}>}sales)

and for previous year sales

sum(<Year={'$(=Max(Year)-1)'}>sales)

hope this will help you..

regards

bhawna

Re: Iterative Year vs Prior Year

Anonymous — Tue, 12 Aug 2014 09:01:31 GMT

hi asfhaq,, thanks for it... very interesting,, i used it a reference..

Hi Bhawna,

thanks for this.. This calculation is working... but when i nest them under a year dimension, they no longer work...

is there a way to revise this calculation where in it will pickup the iterate value of the year column on top of it.. and use that to compute for the current and previous yr... ??

many thanks!