Aggregating calculated dimensions for different levels

[#1]

Hi,

I'm working on a pivot table with one dimension Country, another one City which is visible only when I click on a macro button to show up the detail for City and one calculated dimension which expresses the sales of each Country and - whether I have pressed the button - of each City.

My problem consists in calculating correctly the Sales aggregation for each level (Country or City) because it is a calculated dimension and I can't use the traditional Pick(Dimensionality()....).

In order to not get the error in calculated dimension, the Aggr function is requested, but how can I switch the level of aggregation? Is it possible to do it depending on the dynamic name of the button?

Do you have any ideas?

Thank you

[Anonymous]

Christina,

As I understand (not sure about it), you want to use aggr(<expression>, Country) or aggr(<expression>, Country, City) depending on the situation controlled by the macro button.

In this case, you can use a conditional expression:

if(<condition>, aggr(<expression>, Country), aggr(<expression>, Country, City))

Or a variable:

aggr(<expression>, $(AggrVar))

Where AggrVar is set by your macro to 'Country' or 'Country, City'

Regards,

Michael

Edit: fixed expression

[#3]

Hi Michael,

I tried to do as you suggest me. I used the conditional expression:

if(<condition>, aggr(<expression>, Country, City), aggr(<expression>, Country, City))
but something strange happens. When I analyze data at Country level the data in the calculated dimension aren't displayed. I don't know where the mistake is.

My formula is =if(vCity = 0, aggr(sum(Sales), Country),aggr(sum(Sales), Country, City))

I'm going to explain how I manage my button in order to show/hide the column City.
vCity is my variable set up to 1
For the City dimension I set up Enable Dimension vCity = 1. My macro is working based on this action: =if(vCity = 1,0,1)
When I press on button, the new column City appears and the sales are aggregated by City, but when I press again the button to collapse the City column and to show up the sales only at Country level, the sales values disappear. I can only visualize a hyphen.
If I add a new column containing aggr(sum(Sales), Country), it works...I don't understand why doesn't work in my formula.
Do you have any idea?

Thank you

[Anonymous]

Hi Cristina,

Not clear to me why the problem is.  If the expression aggr(sum(Sales),Country) works, than the conditional version should work as well (given the condition is correct).  Can you attach an example of your application?

If not, you can use a completely different aproach - conditionally enable of dimenstions and expressions.  That is, show City dimensions in vCity=1.  As for the expressions, create two but show one at a time:
aggr(sum(Sales), Country, City)      - enable if vCity=1

aggr(sum(Sales), Country)              - enable if vCity=0

There are often many ways to get what you want in QV.  Hope something will work.

Regards,

Michael