topic Re: Percentage Change Month over Month in QlikView

Percentage Change Month over Month

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Please see below for a pivot table I have created. Right now, the redacted cell values are SUM(AMOUNT), however I would like to track the % change of SUM(AMOUNT) in each cell, not the actual SUM of AMOUNT.

Any help is appreciated.

Thank you,

- dave

Re: Percentage Change Month over Month

sunny_talwar — Fri, 16 Sep 2016 20:33:00 GMT

Change from last period?

May be this:

Sum(AMOUNT)/Before(Sum(AMOUNT)) - 1

Re: Percentage Change Month over Month

Anonymous — Fri, 16 Sep 2016 20:39:54 GMT

Sunny this works immediately thank you. Just a couple of issues:

My data goes all the way back to 2010 but when I use your expression I lose 2010 and the first half of 2011. Any reason you can think of?

Also, is there a way I can express this as 100%. As in - First month of sales = 100% ... If sales increase, 102%, if sales decrease 96%

Thanks again!

Re: Percentage Change Month over Month

sunny_talwar — Fri, 16 Sep 2016 20:44:32 GMT

‌for second issue I would say that don't subtract the one and. Change the number format to percentage on the numbers tab

Sum(AMOUNT)/Before(Sumy(AMOUNT))

Re: Percentage Change Month over Month

sunny_talwar — Fri, 16 Sep 2016 20:46:41 GMT

‌regarding the lose of data I am not really sure what moght be causing this. May be there is nothing available for before. May be try this:

Sum(AMOUNT)/Alt(Before(Sum(AMOUNT)), Sum(AMOUNT))

Re: Percentage Change Month over Month

Anonymous — Fri, 16 Sep 2016 21:04:12 GMT

This is getting closer but it only shows the percentage change when AMOUNT increases from month to month. I know that is what was initially requested but as I start to do analysis I realize things are changing slightly.

Cohort - A, initial month, Oct 2010, amount $3000 = 100%

Cohort A - Nov 2010 - amount $3500 = 116%

Cohort A - Dec 2010 - amount $4000 = 133%

Cohort A - Jan 2011 - amount $2500 = 83%

Does this make sense? Its almost like I would like to calculate the percentage change every month based on the initial month of the Cohort.

Re: Percentage Change Month over Month

sunny_talwar — Fri, 16 Sep 2016 21:13:05 GMT

May be like this:

Sum(AMOUNT)/First(Sum(AMOUNT))

Re: Percentage Change Month over Month

Anonymous — Fri, 16 Sep 2016 21:17:05 GMT

That didnt not work unfortunately. Would it help to do some type of aggr with a min statement?

Something like

sum(amount) / min(sum(aggr(Month, Cohort) Sales))

Re: Percentage Change Month over Month

sunny_talwar — Sun, 18 Sep 2016 16:51:49 GMT

This seems to be working:

=Sum(AMOUNT)/FirstSortedValue(TOTAL <Cohort> Aggr(Sum(AMOUNT), [Fiscal Period], Cohort), Aggr([Fiscal Period], [Fiscal Period], Cohort))

Re: Percentage Change Month over Month

Anonymous — Mon, 19 Sep 2016 16:08:51 GMT

This is still not working for me but I believe I know why.

The AMOUNT is not expressed as you have in your table above. The amount is a SUM of sales for multiple companies that belong to a cohort. An example is provided below:

Re: Percentage Change Month over Month

sunny_talwar — Tue, 20 Sep 2016 00:05:05 GMT

May be this:

=Sum(Amount)/FirstSortedValue(TOTAL <Cohort> {<Key = {"=Sum(Amount) > 0"}>} Aggr(Sum({<Key = {"=Sum(Amount) > 0"}>}Amount), [Fiscal Period], Cohort), Aggr([Fiscal Period], [Fiscal Period], Cohort))

Where Key is a field created in the script like this:

Table:

LOAD Company,

Cohort,

AutoNumber([Fiscal Period]&Cohort) as Key,

Date(Date#([Fiscal Period], 'MMM-YY')) as [Fiscal Period],

Amount;

LOAD * INLINE [

Company, Cohort, Fiscal Period, Amount

A, 1, Jan-10, 100

A, 1, Feb-10, 200

A, 1, Mar-10, 300

A, 1, Apr-10, 300

A, 1, May-10, 500

B, 1, Jan-10, 0

B, 1, Feb-10, 0

B, 1, Mar-10, 0

B, 1, Apr-10, 300

C, 2, Jan-10, 0

C, 2, Feb-10, 0

C, 2, Mar-10, 100

C, 2, Apr-10, 200

D, 2, Jan-10, 0

D, 2, Feb-10, 100

D, 2, Mar-10, 500

D, 2, Apr-10, 700

];