topic Re: get past year data in QlikView

get past year data

Wed, 14 Jan 2015 08:13:31 GMT

Hi,

when I selected 2014 in my year list box, I want to get the 2013 sales data. How can I do this in a chart expression?

Re: get past year data

Colin-Albert — Wed, 14 Jan 2015 08:21:50 GMT

Sum({<Yr={$(=max(Yr) -1)}>} data)

Yr is your year field, data is the field you are summing

Re: get past year data

arulsettu — Wed, 14 Jan 2015 08:34:46 GMT

like this

sum({<year={$(=max(year)-1)}>}sales)

Re: get past year data

Wed, 14 Jan 2015 08:59:31 GMT

Hi,

max(year) means current year or selected year?

Re: get past year data

arulsettu — Wed, 14 Jan 2015 09:01:43 GMT

if your year field like this

2011,2012,2013,2014

max(years) will give 2014

Re: get past year data

Wed, 14 Jan 2015 09:13:19 GMT

Hi arul,

If I selected 2013 in my year list box, is it showing 2012 data for the result?

Re: get past year data

arulsettu — Wed, 14 Jan 2015 09:14:53 GMT

yes based on your selection it will give previous year

Re: get past year data

sunilkumarqv — Wed, 14 Jan 2015 09:15:31 GMT

for curren year =Max(Year) =2014

For Prev Year =Max(Year)-1=2013

in set analysis you need to define like below and also include year=

Sum({<Year={$(=Max(Year)-1)},Year=>}value)

Re: get past year data

Wed, 14 Jan 2015 09:29:18 GMT

But the problem is when I selected year=2014 and month=12, I m getting same results for the two columns. why is that?

these are my expressions in my chart.

Num(Sum({<Year={'$(=Max(Year)-1)'},Year=>}Retail_Sale_Qty),'#,##0') - prev year

Num(Sum(Retail_Sale_Qty),'#,##0') - selected year

Re: get past year data

ashfaq_haseeb — Wed, 14 Jan 2015 09:31:44 GMT

Hi try like this

Num(Sum({<Year={'$(=Max(Year)-1)'}>}Retail_Sale_Qty),'#,##0') - prev year

Num(Sum(Retail_Sale_Qty),'#,##0')

Regards

ASHFAQ

Re: get past year data

sunilkumarqv — Wed, 14 Jan 2015 09:33:12 GMT

Include 'Month= ' as well

Num(Sum({<Year={'$(=Max(Year)-1)'},Year=,Month=>}Retail_Sale_Qty),'#,##0') - prev year

Num(Sum(Retail_Sale_Qty),'#,##0') - selected year

Re: get past year data

Wed, 14 Jan 2015 09:36:03 GMT

Hi sunil,

I can't neglect the month because I want december month sales data

Re: get past year data

sunilkumarqv — Wed, 14 Jan 2015 09:40:40 GMT

its not month restriction that intense to trigger will happen have try and let me no

Re: get past year data

Wed, 14 Jan 2015 10:29:03 GMT

my problem is still the same

Re: get past year data

ashfaq_haseeb — Wed, 14 Jan 2015 10:30:31 GMT

HI,

did you try my solution?

If possible share sample app

Regards

ASHFAQ

Re: get past year data

Wed, 14 Jan 2015 10:42:38 GMT

I tried, but no change

Re: get past year data

Wed, 14 Jan 2015 11:26:23 GMT

Please find the attached QVW

Re: get past year data

marchoctober — Wed, 14 Jan 2015 14:21:18 GMT

Also simple way is using Only,

F.e. you select year 2014, there will be expression only(year)-1, that will handle it,

sum({<year={$(=Only(year))}>} Sales) //First expr that calculates selected year,
sum({<year={$(=Only(year)-1)}>} Sales) //Second expr that calculates year-1,

='Sales '&Only(year_sale) // First expr to label calculation for selected year,
='Sales '&(Only(year_sale)-1) // Second expr to lavel calculation for year-1,

For ease of use you can on general tab of chart properties identify Calculation condition, f.e. Count(DISTINCT year_sale)=1

This means, that chart will shown up only when you select needed (starting) year which will be the basis to count previous year.

Don't forget to set ERROR message that will helps the user choose the year, go to Error messages on General tab of chart properties, and further locate Calculation condition unfulfilled and set reminer for something like that: Please select sales year.

User will see the message in chart, further select the year 2015, and he will see results for 2015 and 2014, if he selects, f.e. 2013, he will see 2013 vs. 2012.

Hope it also will help.

Re: get past year data

jagan — Wed, 14 Jan 2015 15:31:13 GMT

Hi Dushan,

Try this expression

Sum({<Year={$(=(Max(Year)-1))},Month=,Quarter=, Week=, Date=>}value)

Replace Month,Quarter, Week, Date with corresponding fields in your dashboard.

Regards,

Jagan.

Re: get past year data

Thu, 22 Jan 2015 10:10:00 GMT

thanks all