Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
cycle groups are very useful because you can display your expression with different dimensions. I've this simple pivot table
Countries USA ITALY ......
Customer TotalSales Sales % SAles %
Sales% represents the percentage of Total Sales for country.
Total sales is calculated by aggr (sum(salesamount), Customer).
My question now: I would create a cycle group composed by Customer, Salesman and Brand. Is it possible to use the cycle group in the Total sales expression ?
In short terms, is it possible to use the group in the aggr expression, or is it possible to get the current group dimension in the aggr function, like this:
aggr(sum(.....), selected_group_dimensio) where selected_group_dimension changes everytime I click on the group ? Is it possible to roll dimensions in aggr function dynamically ?
Thanks for your help
The quotes is obsolete, so the right expression is:
aggr(sum(...), $(=getcurrentfield(Group_Name)))
Everithing works fine.
I get the message Error dimension
Don't understand. Why did you try to place this expression as calculated dimension ?
Hi.
There is a function getcurrentfield() that you could use to get the name of field actiually selected by cycle group.
Then you could use $() expansion to substitute it to your expression dynamically.
aggr(sum(...), $(=getcurrentfield('Group Name')))
Hi Whiteline,
thanks for your help, but it doesn't work. I get the message Error dimension. It seems it doesn't recognize the Getcurrentfields(My_group)
The quotes is obsolete, so the right expression is:
aggr(sum(...), $(=getcurrentfield(Group_Name)))
Everithing works fine.
I get the message Error dimension
Don't understand. Why did you try to place this expression as calculated dimension ?
Thanks!!! it works fine.
What I'm trying to do is a pivot with dynamic dimensions, using a cycle group.
Having one dimension only (for example customer), the TOTALSALES is aggr (sum(salesamount), Customer).
If I want to do the same for another dimension, I've to create another pivot and my aggr became
aggr(sum(salesamount), New_dimension).
So I would have just one pivot changing the dimension in the aggr function.
This is what I thought: I'm newer and perhaps there are severals better ways than mine. Any suggestion and ideas are really appreciated !!
Thanks for your time
Sorry, I don't understand, why don't you use getcurrentfield()?
I'm trying to explain that you can do what you want with one pivot using getcurrentfield() function within expression.
Just use
cyclic group My_group as dimension
and
=aggr(sum(salesamount), $(=getcurrentfield(My_group))) as expression.
The corresponding field name will be substituted in the expression.
Look at the screen provided above, there is cyclic group as dimension and expression with getcurrentfield().
You can see that the corresponding dimension value 'B' has been substituted instead of getcurrentfield().
Isn't it what you want ?
Yes, perhaps I wasn't clear too much, but what you wrote is what I want to reach: one pivot table, with cycle group and aggr function directly related to the current_grou_dimension by the "getcurrentfield"
Hello
This post is of great help.
I had to use quotes because there are cases that are created field names that are separated , causing error.
Example: [City Name]
"$(=getcurrentfield(Group_Name))"
Thank you!