topic Variance% in Pivot in QlikView

Variance% in Pivot

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

Hi all,

In pivot chart i am comparing current year sales and previous year sales and their Variance %

I am using this formula for variance %

=(sum({<Year={$(=Max(Year))}>}SalesValue)

sum({<Year={$(=Max(Year)-1)}>}SalesValue))

sum({<Year={$(=Max(Year)-1)}>}SalesValue)

and in visual cues i selected Upper>=1 and Lower<=0

When current year has vales and last year doesnt have values it displays '-' in variance% but i want to display %values.

PFA for screenshot

Regards,

Joshua.

Re: Variance% in Pivot

shiveshsingh — Fri, 29 Dec 2017 07:16:44 GMT

=(sum({<Year={$(=Max(Year))}>}SalesValue)

sum({<Year={$(=Max(Year)-1)}>}SalesValue))

sum({<Year={$(=Max(Year)-1)}>}SalesValue)

as per your formula, if we don't have value in denominator, result would not be defined. That's why it is coming as '-'

What % are you expecting if denominator is 0 or null?

Re: Variance% in Pivot

devarasu07 — Fri, 29 Dec 2017 07:25:39 GMT

Hi,

try to use Alt function,

like this

method 1:

= alt((sum({<Year={$(=Max(Year))}>}SalesValue) - sum({<Year={$(=Max(Year)-1)}>}SalesValue))

/ sum({<Year={$(=Max(Year)-1)}>}SalesValue),0)

method 2: simplified one,

alt((Column(1) -Column(2))/Column(2),0)

Thanks,
Deva

Re: Variance% in Pivot

pradosh_thakur — Fri, 29 Dec 2017 07:50:30 GMT

We encountered smilar divide by zero issue few months ago. My Client wanted to make it 100% when last year is zero.So it will depend on business requirement.

= alt((sum({<Year={$(=Max(Year))}>}SalesValue) - sum({<Year={$(=Max(Year)-1)}>}SalesValue))

/ sum({<Year={$(=Max(Year)-1)}>}SalesValue),1)

Re: Variance% in Pivot

ajsjoshua — Fri, 29 Dec 2017 08:37:12 GMT

Dear Devarasu,

Thanks for ur reply

Its working

= alt((sum({<Year={$(=Max(Year))}>}SalesValue) - sum({<Year={$(=Max(Year)-1)}>}SalesValue))

/ sum({<Year={$(=Max(Year)-1)}>}SalesValue),1)

but when i am setting visual cues as

upper>=21 as green and Lower<=20 as red it display 100% in red but i want to display it in green.

Re: Variance% in Pivot

ajsjoshua — Fri, 29 Dec 2017 08:37:30 GMT

Dear Pradosh,

Thanks for ur reply

Its working

= alt((sum({<Year={$(=Max(Year))}>}SalesValue) - sum({<Year={$(=Max(Year)-1)}>}SalesValue))

/ sum({<Year={$(=Max(Year)-1)}>}SalesValue),1)

but when i am setting visual cues as

upper>=21 as green and Lower<=20 as red it display 100% in red but i want to display it in green.

Re: Variance% in Pivot

pradosh_thakur — Fri, 29 Dec 2017 08:42:32 GMT

Use 0.21 and 0.20 in place of 21 and 20

Re: Variance% in Pivot

ajsjoshua — Fri, 29 Dec 2017 08:54:36 GMT

Dear Pradosh,

THank You.