topic cycle groups in QlikView

cycle groups

Anonymous — Sat, 03 Nov 2012 13:31:40 GMT

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

Re: cycle groups

whiteline — Sat, 03 Nov 2012 14:35:43 GMT

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

Re: cycle groups

Anonymous — Sat, 03 Nov 2012 16:56:24 GMT

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)

Re: cycle groups

whiteline — Sat, 03 Nov 2012 18:15:00 GMT

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 ?

Re: cycle groups

Anonymous — Sun, 04 Nov 2012 09:48:52 GMT

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

Re: cycle groups

whiteline — Sun, 04 Nov 2012 10:23:20 GMT

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 ?

Re: cycle groups

Anonymous — Sun, 04 Nov 2012 10:54:53 GMT

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"

Re: cycle groups

michaelfreitas — Tue, 06 Oct 2015 14:07:52 GMT

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!