topic Re: Set Analysis to Show % Change Automatically in QlikView

Set Analysis to Show % Change Automatically

Anonymous — Tue, 07 Mar 2017 16:54:55 GMT

I have a pivot table (pictured below) with 1 dimension, OppCreateMonth and one measure:

=sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])

Is there a way to have an expression that automatically calculates the percentage change between each cell as you move right? This would allow me to write ONE expression and then graph the percentage change between periods at any level.

Thank you,

- dave

Re: Set Analysis to Show % Change Automatically

sunny_talwar — Tue, 07 Mar 2017 16:58:10 GMT

May be this

=Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])/

Before(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])) - 1

=Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])/

After(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])) - 1

Re: Set Analysis to Show % Change Automatically

Anonymous — Tue, 07 Mar 2017 17:03:49 GMT

Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])/

Before(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])) - 1

The BEFORE and AFTER commands are what I was missing.

Thanks as always Sunny

Re: Set Analysis to Show % Change Automatically

sunny_talwar — Tue, 07 Mar 2017 17:06:18 GMT

Before and After are function unique to Pivot table, so don't get noticed by a lot of people. Similarly, there is ColumnNo() function which works like RowNo() but with a pivoted dimension....

Re: Set Analysis to Show % Change Automatically

Anonymous — Tue, 07 Mar 2017 17:11:10 GMT

Is there a similar function to Before and After in a straight table?

Re: Set Analysis to Show % Change Automatically

sunny_talwar — Tue, 07 Mar 2017 17:15:08 GMT

Above and Below

Re: Set Analysis to Show % Change Automatically

HirisH_V7 — Tue, 07 Mar 2017 17:23:04 GMT

Hi,

Check this

IF((Above(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)]))-Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)]))<0,-(Above(Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)]))-Sum({<[Upsell ARR (converted)]={">0"}>}[Upsell ARR (converted)])),
(Above(Sum(Sales)/Sum(TOTAL Sales))-Sum(Sales)/Sum(TOTAL Sales)))

Using this in combo chart line expression, you can have line for only difference on bar chart.

Attached the sample QVW.

HTH,

PFA,

Hirish

Re: Set Analysis to Show % Change Automatically

Anonymous — Tue, 07 Mar 2017 17:47:10 GMT

Ha thank you!