7 Replies Latest reply: Oct 6, 2015 10:07 AM by Michael Freitas RSS

    cycle groups

    steve peroni


      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 _



          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
              steve peroni

              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 _

                  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
                      steve peroni

                      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 _

                          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


                          =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
                              steve peroni

                              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
                      Michael Freitas


                      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]


                      Thank you!