Problems with refering to other period i Pivot Table by using Set Analysis

[#1]

Hi,

I currently have a Pivot Table where I have the 4 dimensions where the last one (Period) is moved to a column dimension. I'm trying to add a expression for comparing a field Billable_Hours against the last Period without any luck.

I've tried to add a expression like this to see if I get it working (Refer to a specific period before trying to add any calculated reference):

Sum({<Period = {'Uke 42'}>} [Billable_Hours])

The problem is that this returns 0 for all other Period columns than the one named "Uke 42".

Anything I'm missing here?

Thanks in advance,

Christian

[Gysbert_Wassenaar · Accepted solution]

What you're missing is that the result of the set is intersected with the dimensions of the chart. The intersection of the dimension values of Period with the value Uke 42 will result in an empty set for all Period values except the value Uke 42 (i.e. itself). If you want the result of Sum({<Period = {'Uke 42'}>} [Billable_Hours]) as value in the expression column for every Period value you can evaluate it outside the context of the chart by using dollar expansion: =$(=Sum({<Period = {'Uke 42'}>} [Billable_Hours]))

[#3]

Aah, that make sense! Thank's a lot