Deviation across columns

[#1]

Hello all,

I have a question, which I assume can be resolved easily, it's just that I cannot see it ....

I have a simple table: Years, CarTypes, Revenue

I have a stacked bar chart: 1 bar for each Year, 1 segment for each CarType, summing up the revenue.
That's simple.

I want to have a stacked bar chart, which calculates the devation to the previous year.
1 bar for each Year, 1 segment for each CarType, summing up the deviation.
Obviously the first year disappears (no prev. year).
This must be simple as well ... [*-)]

Thanks for any hint,
Thilo

[#2]

Hello,

Really Nothing?
I thought it is a simple question where I am just too blind too see the solution.

Is "no reply" an indication, that it is not that simple?

Thanks for any info,
Thilo

[johnw]

Speaking for myself, no reply here meant that I didn't understand your question. It might be helpful if you posted example data and example intended results. It might well be a simple question with a simple solution, but I can't really tell.

[#4]

Hello John,

thanks for your hones reply.

attached find the sample app which hopefully explains what I want to achieve.
Thank you,
Thilo

[johnw · Accepted solution]

The deviation is wrong because above() refers only to the last dimension in the list. Since CarType is your last dimension, it is showing the deviation between CarTypes, not the deviation between years. Of course if you merely swap the dimensions, you might get the right numbers, but the format of the table will be all wrong.

One solution would be if you have a limited number of CarTypes to display. You could then use set analysis to make a separate expression for each CarType. Since it wouldn't be a dimension, the last dimension would be year, and above() and your deviations would thus work correctly. See attached for an example.

As you mention in the example, calendar tricks are another solution. See attached.

I consider the calendar tricks version to be more robust. If you add another CarType, you don't need to change the chart. Also, consider what happens if you select a specific year from the chart. The set analysis version returns no data - you have to select two years in order to get data for the second year. The calendar tricks version works as expected, showing deviation for only that year. On the other hand, you now have two different year fields instead of one. If you select Year instead of AsOfYear, the Calendar Tricks chart will return nonsense data, which is probably even worse than "no data to display" from the set analysis version.

Whichever works best for you and your users, I guess.

[#6]

Hello,

thank you for the reply.
As the real-life situation is a bit more complex than the 3-CarTypes-example, the 'calendar tricks'-version must do the job ... and as you mentioned, it is more robust.

Thanks again,
Thilo