Pivot Table Dimension (Above expression)

[#1]

Pic 1

Pic 2

Just a question, when i change the Dimension of Month From Row to Column the Background Colour Expression is wrong.

The results in Pic 1 i posted is what i wanted, but i want to remain the correct colouring but the Month would be display on the Column instead of Row.

My expression for the Background Colour :

if(sum(BAL) > Above(sum(AL)), rgb(51,255,51),

if(sum(BAL) = Above(sum(BAL)), rgb(255,255,102),

if(sum(BAL) < Above(sum(BAL)),rgb(255,51,51), rgb(255,255,255))))

[swuehl · Accepted solution]

If you pivot Month dimension to the top, you need to change Above() to Before():

if(sum(BAL) > Before(sum(AL)), rgb(51,255,51),

if(sum(BAL) = Before(sum(BAL)), rgb(255,255,102),

if(sum(BAL) < Before(sum(BAL)),rgb(255,51,51), rgb(255,255,255))))

[#3]

Thanks a lot, it works.

With this formula does Jan 2014 compare with Dec 2013 itself too?

[swuehl]

Only if you have both dimension values listed in your table, Dec 2013 left from Jan 2014.

[#5]

So if i have a list box to choose every year then mostly Jan will not be able to compare with the previous year Dec, did i get what you said correctly?

[swuehl]

Not if you just use above expression.

However, you can achieve what I think you want by using advanced aggregation, something along the lines Elif Tutuk described here (in the very last example):

Accumulative Sums

sum( aggr( rangesum( above( sum( {$<Month>}  Sales),0,12) ),Month))

Take care, that when using chart inter record functions inside the aggr(), you need to use above() / below(), even when your Month dimension is pivoted in the chart.

You also need to take care that the Load order of the relevant aggr() dimensions (Month in this case) is sorted chronological.

Read also

Pitfalls of the Aggr function‌

You see, it's probably possible, but not a trivial one.

Alternatively, you can again look at the first link in this reply or

The As-Of Table

and prepare the link to the values (of Previous Month) you need to look up in the data model.