Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
573
How can I add formula to groupping rows
posted

 I have a dataset on which I am using the GroupBy fields displayed like this

    Group By > Category : Computers (3 items)

                              ItemID                   Sold2009                   Sold2008                          Diff                   %

                              0001                       10                                           7                      3              30%       

                              0002                       5                                             3                      2              40%       

                              0003                       2                                             6                      -4             -200%       

                                                  Sum= 17                                     Sum=16              Sum=1

                    Category : Printers (2 items)

                             0004                      6                                                5                      1               16%

                             0005                      7                                                2                      5                71.41%

                                               Sum = 13                                       Sum=7             Sum=6

                                            Sum 30                                       Sum 23                   Sum=7

 where column % is formula = ([Diff]/if([Sold2009]=0,1,[Sold2009]))  * 100

How can I make a formula for % column for each category

to lock like this

 

    Group By > Category : Computers (3 items)

                              ItemID                   Sold2009                   Sold2008                          Diff                   %

                              0001                       10                                           7                      3              30%       

                              0002                       5                                             3                      2              40%       

                              0003                       2                                             6                      -4             -200%       

                                                  Sum= 17                                     Sum=16              Sum=1           5.88%

                    Category : Computers (2 items)

                             0004                      6                                                5                      1               16%

                             0005                      7                                                2                      5                71.41%

                                               Sum = 13                                       Sum=7             Sum=6               46.15%

                                            Sum 30                                       Sum 23                   Sum=7             23.33%

 

I try to add this formula 

([//UltraGrid1/Table/Diffsum()]/if([//UltraGrid1/Table/Sold2009sum()]=0,1,[//UltraGrid1/Table/Sold2009sum()]) * 100 

but give me like this

    Group By > Category : Computers (3 items)

                              ItemID                   Sold2009                   Sold2008                          Diff                   %

                              0001                       10                                           7                      3              30%       

                              0002                       5                                             3                      2              40%       

                              0003                       2                                             6                      -4             -200%       

                                                  Sum= 17                                     Sum=16              Sum=1          23.33%

                    Category : Computers (2 items)

                             0004                      6                                                5                      1               16%

                             0005                      7                                                2                      5                71.41%

                                               Sum = 13                                       Sum=7             Sum=6               23.33%

                                            Sum 30                                       Sum 23                   Sum=7             23.33%

 

 

 

 Thank you

 

 

 

 

Parents
  • 469350
    Verified Answer
    Offline posted

    I think the problem might be that you are referencing the summaries in your formula using absolute references.You want you summary to use the summaries that are on the sme island of rows. That is to say, you want relative summaries, not absolute ones. 

    Try something like this:

    ([Diffsum()]/if([Sold2009sum()]=0,1,[Sold2009sum()]) * 100 

     

Reply Children
No Data