topic Re: Sales on Year minus Sales on Previous Year in QlikView

Sales on Year minus Sales on Previous Year

Wed, 06 May 2015 23:07:13 GMT

Hello everyone

I've been looking for an answer for quite a while, without success.
Here's the problem:

I need to create a chart where there'll be something like this

Year Value Growth

2000 10 N/A

2001        40            30
2002        20           -20
2003        40            20

Don't pay to much attention on the values, it's just an example.
I actually need to compare values of a same Element on two different dates

I'd write it like this: sum(SALES[YEAR]) - sum(SALES[YEAR-1])
but apparently, it doesn't work that way.

I also tried by creating variables like vYear = YEAR and vPrevYear = YEAR - 1
But nothing works 😕

Please help !

Re: Sales on Year minus Sales on Previous Year

Gysbert_Wassenaar — Thu, 07 May 2015 09:47:26 GMT

You can try sum(Value) - sum(above(Value).

Or read this document: Calculating rolling n-period totals, averages or other aggregations

Re: Sales on Year minus Sales on Previous Year

Thu, 07 May 2015 23:12:31 GMT

Thanks, it worked quite well for one of them, also used the below() for another one, but I still have an issue for where they're not right above/underneath each other.

In the picture, the last blocks (the ones with 0) need to show the growth for each type of object year to year.
So the above works well when nothing is expanded, as years appear right above each others, but when I expand the objects fields, it's not working anymore 😕

Thanks again

Re: Sales on Year minus Sales on Previous Year

maksim_senin — Fri, 08 May 2015 04:06:38 GMT

Hi Matthieu,

Also please don't forget about set analysis mechanism which is far stronger then inter-record functions.

With this you can state:

Sum({<Year={2000}>} Value) = Sum({<Year={2001}>} Value)

Define variables vCurrYear and vPrevYear and then use them:

Sum({<Year={$(vCurrYear)}>} Value) = Sum({<Year={$(vPrevYear)}>} Value)

It's event possible to fold all set analysis expression into variable - vCurrYear = Year={$(vCurrYearNum)}

(not sure the syntaxis is correct everywhere, but the idea is clear I hope)

Best regards,

Maxim

Re: Sales on Year minus Sales on Previous Year

Gysbert_Wassenaar — Fri, 08 May 2015 07:31:52 GMT

The inter-record functions like above, below, before etc.. by default don't cross dimension borders. By adding the TOTAL qualifier they will cross dimension borders. Unfortunately it's a all or nothing, either none or ignore all dimension borders. In case of your pivot table you could try pivoting your year dimension to horizontal layout by dragging its header to the right above the expressions. There'll be only the year dimension horizontally so sum(value) - before(sum(value)) should work.

If that's not a workable solution you can try the AsOf solution described in the document Calculating rolling n-period totals, averages or other aggregations

Re: Sales on Year minus Sales on Previous Year

ankitaag — Fri, 08 May 2015 07:56:11 GMT

Hi,

PFA the attached application.

Thanks and Regards,

Ankita

Re: Sales on Year minus Sales on Previous Year

Fri, 08 May 2015 13:00:04 GMT

Thanks for all the answers,

I managed to get to something like this

Sum({<CALENDAR_YEAR={$(vYear)}>} AMOUNT_SOLD/PROD_LIST_PRICE) -

Sum({<CALENDAR_YEAR={$(vPreviousYear)}>} AMOUNT_SOLD/PROD_LIST_PRICE)

(with vYear = CALENDAR_YEAR and vPreviousYear = CALENDAR_YEAR-1)

But it doesn't gets me what I need, here's what comes when I only select the 1999 year:
(Somme des quantitées vendues works with this expression: "sum(AMOUNT_SOLD/PROD_LIST_PRICE)")

And here's what I got when nothing is selected:

The idea is to just show 1999 rows, with in the first expression column those values (well, without the 0 for 1998 obviously) and in the second one getting sum(AMOUNT_SOLD/PROD_LIST_PRICE) for year in row MINUS sum(AMOUNT_SOLD/PROD_LIST_PRICE) for year before that

Thanks again for your help !