cycle groups

[Anonymous]

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

[whiteline]

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')))

[Anonymous]

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)

[whiteline · Accepted solution]

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 ?

[Anonymous]

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

[whiteline]

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 ?

[Anonymous]

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"

[michaelfreitas]

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!