topic Re: Last year sales averaged by issue date in QlikView

Last year sales averaged by issue date

Tue, 20 Mar 2012 11:00:55 GMT

Hi All,

I would really appreciated if someone could help with this function as it doesn't work at all.

avg(aggr(sum(SUPPLY_QUANTITY),ISSUE_DATE)) and this gives me the sales for the selected year as a monthly average

now I would like to have the same data for previous year based on the selected year.

avg(aggr(sum(if(Cal_Year=vLY,SUPPLY_QUANTITY)),ISSUE_DATE))

Cal_Year is a field

vLY is a variable which is =Cal_Year-1

but for some reasons it doesn't work although it's set to last year?

if this helps I've attached picture of how the data is presented.

jvitantonio — Tue, 20 Mar 2012 11:06:53 GMT

Try this:

avg(aggr(sum( {$<Cal_Year = {$(#vLY)}>} SUPPLY_QUANTITY ),ISSUE_DATE))

Tue, 20 Mar 2012 11:11:28 GMT

Hi,

Thanks for your help but unfortunately it's still showing no data, it works with summing but not with adding avg(aggr.

Regards,

Redha

CELAMBARASAN — Tue, 20 Mar 2012 11:41:24 GMT

Hi,

How you Created the Year filter?is it Cal_Year?

if so try this

avg({$<Cal_Year = {$(vLY)}>} aggr(sum( {$<Cal_Year = {$(vLY)}>} SUPPLY_QUANTITY ),ISSUE_DATE))

Celambarasan

Tue, 20 Mar 2012 12:22:40 GMT

Hi Calambarasan,

Thank you so much you've saved my life as it's been a while trying to figure this out.

Thanks again

Regards,

Redha