Variance% in Pivot

[ajsjoshua]

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.

[shiveshsingh]

=(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?

[devarasu07 · Accepted solution]

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

[pradosh_thakur]

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)

[ajsjoshua]

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.

[ajsjoshua]

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.

[pradosh_thakur]

Use 0.21 and 0.20 in place of 21 and 20

[ajsjoshua]

Dear Pradosh,

THank You.