Pivot table %

[cbushey1]

Hi there,

I am looking for a way for my percentage expression to only display a percentage based on the total that is displaying. I understand that typically you can use the TOTAL prefix in the expression to get the data across the whole database but I need to only display percentages across the 12 months that are displaying.

My Numerator expression is as follows:

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

The two fields I am aggregating my CaseCounter over are the dimensions displayed in the pivot chart.

Suggestions?

[sunny_talwar]

So I am guessing that you are using Rolling12M_Flag to display 12 month period? If that is true, you should be able to do just this:

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))/Sum(TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

[cbushey1]

Hi Sunny,

Thanks for the post, however I have tried that and it doesn't return the expected results. The problem is that the Total is ignoring my dimensions of Date_YM (i.e. October 2015...) and Cancellation reason and summing all values together. I know I have data outside of the last 12 months and unfortunately that is being included in the denominator.

I have also tried moving total to the inside Sum and many other variations with the Aggr to no avail.

Any other thoughts?

[sunny_talwar]

It might help to see a sample, but try these if you have not already done so

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))/Sum({<Rolling12M_Flag = {1}>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

Also, are you using calculated dimension or dimension limits in your chart?

[cbushey1]

I think this might have done it for me.

Is it possible to have this calculation ignore nulls?

I found I also have some nulls in my CancellationReason field.

[sunny_talwar · Accepted solution]

In the denominator or numerator or both?

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>} CaseCounter), Date_YM, CancellationReason))/Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>}CaseCounter), Date_YM, CancellationReason))

[avinashelite]

may be

Sum(Aggr(Sum({<Rolling12M_Flag = {1},CancellationReason ={'*'}-{''}>}CaseCounter),Date_YM,CancellationReason))/Sum({<Rolling12M_Flag = {1},CancellationReason ={'*'}-{''}>}CaseCounter>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

[cbushey1]

Thanks! That did the trick for me.