cancel
Showing results for
Did you mean:
Creator

## Variance % and Δ Year vs Prev-Year Dynamically

Hi all!

I am in stuck with an expression who doesn't give right data.

Easily, what I am trying to reach is something I've already found on others discussions for Variance calculation but - I am doing something wrong for sure in my expression;

have a look here:

I need to calculate the "difference" in Variance % from one year to other (basically, ThisYear vs LastYear and LastYear vs PrevYear - 36 months range) considering "Absolute" and "Relative" and between Value and Volume

To be more clear:

2013                2014                    2015

Company A     15.000.000          16.000.000         7.000.000

Company B       9.000.000         10.000.000          5.000.000

Company C       5.000.000           7.000.000          3.000.000

I Need to know Variance in % between Years and its relative % difference.

Now, the expression for calculating Variance I've used is the following:

=Sum({<Regions -={'5000'}>}Sales)/Sum({<Regions -={'5000'}>}Total Sales)

-

/

Herewith you can find a mere sample of what I Mean (Expression Above is the one I use in real project - not in the sample).

The result are same: I can see a % that is not what I need, which is: if Variance in 2014 was 40% and in 2015 is 30%, I need to display the difference of 10%.

Is that possible?

Thank you all!

Alex

1 Solution

Accepted Solutions
Specialist III

This should do.let me know.

14 Replies
Specialist III

Try using above function in your chart.

See attached.

Partner - Creator II

Just refer to the Column

above(Column(1))-Column(1)

or if it's a pivot table you may need to use before(Column(1)) etc

Creator
Author

Thank you krishna chaitanya!

Seems working...and I've already implemented in real.

However...some data still doesn't give correct results, but maybe I need to fix something.

Apart from that, even in the sample you kindly resend me, I see that data value change on selection (ie: if you select Company data change and worse is Year selection who made "-" result).

Is possible to avoid this behavior?

Or - I don't know - maybe a way to fix just the result in a new column "disconnected" from other possible selections?

Creator
Author

Thanks to you too sgrice!

Well...I am not really sure where to put your suggestion.

I try out on the sample, it still works, but it looks like no other selection is possible...like panels are freeze and selections have no effects.

sUM(sales)/sUM(TOTAL sales)-above(Column(1))-Column(1)and (sUM(sales)/sUM(TOTAL sales))

Is that wrong?

Specialist III

i did not understand the highlighted text.

Apart from that, even in the sample you kindly resend me, I see that data value change on selection (ie: if you select Company data change and worse is Year selection who made "-" result).

Is possible to avoid this behavior?

Creator
Author

Here I am  krishna_2644

Probably my bad english doesn't help...

however, here what I meant.

Look at the data in normal displaying:

If I select any of the company:

If I select any of the year:

I don't understand why value data are changing and - or if its normal behavior to change, I need to know why they are

changing on selection.

(I am expecting difference data if I change year selection for instance, but not if I am selecting one of the company.

Why data are changing?)

Is more clear?

Partner - Creator II

If you are just wanting difference in Column 2 [%]

Just this may work better

above(Column(2))-Column(2)

Also if you do not need it to be a pivot Table then use straight table [They have less problems]

Creator
Author

Ok.

I've tryed also with straight but still have some problem.

I will work on it a little and see if its possible to arrange something.

In the meanwhile, thank you!

Specialist III

IS THIS WHAT YOU WANTED?

Community Browser