11 Replies Latest reply: Dec 6, 2016 2:04 PM by Sunny Talwar RSS

    Alter the TOTAL Qualifier

    Sarah Stefancies

      I am working with the TOTAL qualifier, trying to get proper percentage of totals.

       

      I'm running into a problem where TOTAL does not return a SUM:

       

      table4.png

       

      Service Inv Amt =

      Sum( {$<[Invoice-Type]={'SER'}>}   [invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+

      [invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]

      +[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]

      +[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10])

       

       

      If left alone, the total in that column would be 128,140,264.67.  However, that does not accurately reflect the total of the column.  Changing the "Totals function" to "Sum" fixes it.  However, this does not fix the way the total qualifier works.  The "Total" column is:

       

        ( sum(total {$<[Invoice-Type]={'SER'} >}  

             

              [invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+

      [invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]

      +[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]

      +[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10]) )

       

       

      How can I force the TOTAL column to show the actual SUM of my expression?

        • Re: Alter the TOTAL Qualifier
          Sunny Talwar

          Can you try this:

           

          Sum(TOTAL {$<[Invoice-Type]={'SER'}>}

          RangeSum([invoice.Gross-amt], [invoice.Extra-Charge__1], [invoice.Extra-Charge__2], [invoice.Extra-Charge__3], [invoice.Extra-Charge__4], [invoice.Extra-Charge__5], [invoice.Extra-Charge__6], [invoice.Extra-Charge__7], [invoice.Extra-Charge__8], [invoice.Extra-Charge__9], [invoice.Extra-Charge__10]))

          • Re: Alter the TOTAL Qualifier
            Stefan Wühl

            Seems like you want to calculate a sum-of-rows instead of evaluating the expression in total context.

             

            Try something like

             

            =Sum( TOTAL

            Aggr(

            YOUREXPRESSION, YOURCHARTDIM1, YOURCHARTDIM2, YOUCHARTDIM3

            ))

             

            or

             

            =$(=Sum(

            Aggr(

            YOUREXPRESSION, YOURCHARTDIM1, YOURCHARTDIM2, YOUCHARTDIM3

            ))

            )

            • Re: Alter the TOTAL Qualifier
              Sarah Stefancies

              The first suggestion didn't change the total.  I apologize - I failed to mention that the reason that the sum of "Service Inv Amt" is so much lower is because limitations have been placed on the dimensions to only show lines where the "Service Inv Amt" is larger than 1500. 

               

              That being said, one of the things I tried was to put something in the set analysis of the "Total" statement to only include amounts that are greater than 1500.  This failed because set analysis can only measure field names (not calculated measures) 

               

              Using the second suggestion and bringing in AGGR might be useful but I'll have to play around with it.  Both of the specific AGGR suggestions brought the number down from 128 mil to 91 mil... I'm working on where that number is coming from.

               

              Thanks for the suggestions thus far!

                • Re: Alter the TOTAL Qualifier
                  Sunny Talwar

                  What is your calculated dimension?

                    • Re: Alter the TOTAL Qualifier
                      Sarah Stefancies

                      vInvAmt is a variable the is defined as:

                       

                      [invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+

                      [invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]

                      +[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]

                      +[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10]

                       

                       

                      The idea was to use it in set analysis:

                       

                       

                      sum(TOTAL {$<[Invoice-Type]={'SER'}  , vInvAmt={">1499"}  >}  

                             

                      [invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+

                      [invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]

                      +[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]

                      +[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10])

                       

                       

                      I realize that variables are supposed to be in proper format ( $(vVariable) ) but that doesn't work in set analysis.  Using the variable without the formatting yields the same number that it did without using the variable in the set statement. (128,140,264.67).

                        • Re: Alter the TOTAL Qualifier
                          Stefan Wühl

                          You can only use fields from your data model left of the equal sign in a set analysis field modifier.

                            • Re: Alter the TOTAL Qualifier
                              Sunny Talwar

                              How about this?

                               

                              Sum(TOTAL {$<[Invoice-Type]={'SER'}, [SO No.] = {"=[invoice.Gross-amt]+[invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+[invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]+[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]+[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10]>1499"}>}

                              [invoice.Gross-amt]+ [invoice.Extra-Charge__1]+[invoice.Extra-Charge__2]+[invoice.Extra-Charge__3]+[invoice.Extra-Charge__4]+[invoice.Extra-Charge__5]+[invoice.Extra-Charge__6]+[invoice.Extra-Charge__7]+[invoice.Extra-Charge__8]+[invoice.Extra-Charge__9]+[invoice.Extra-Charge__10])

                                • Re: Alter the TOTAL Qualifier
                                  Sarah Stefancies

                                  I think this is the one!  The total is off but only slightly.  I'm investigating, but believe that it's likely due to anomalous data.  This is really going to help me in the future as well.  I had no idea that you could analyze a calculation in set analysis using a field.  Like Stefan said, you can only use fields from the data model left of the equal sign.  This was impeding my progress. 

                                   

                                  Thank you so much!! 

                                    • Re: Alter the TOTAL Qualifier
                                      Sunny Talwar

                                      One thing you might want to check is that you want to check the Sum of all these fields over a SO No. or another unique field? This field should not have any null values for the 10 fields you are summing up. because any row where one of these values is null, the calculation for the row will fall out.

                                       

                                      You can try this also and see if it fixes the small discrepancy you are still having:

                                       

                                      Sum(TOTAL {$<[Invoice-Type]={'SER'}, [SO No.] = {"=RangeSum([invoice.Gross-amt], [invoice.Extra-Charge__1], [invoice.Extra-Charge__2], [invoice.Extra-Charge__3], [invoice.Extra-Charge__4], [invoice.Extra-Charge__5], [invoice.Extra-Charge__6], [invoice.Extra-Charge__7], [invoice.Extra-Charge__8], [invoice.Extra-Charge__9], [invoice.Extra-Charge__10])>1499"}>}

                                      RangeSum([invoice.Gross-amt], [invoice.Extra-Charge__1], [invoice.Extra-Charge__2], [invoice.Extra-Charge__3], [invoice.Extra-Charge__4], [invoice.Extra-Charge__5], [invoice.Extra-Charge__6], [invoice.Extra-Charge__7], [invoice.Extra-Charge__8], [invoice.Extra-Charge__9], [invoice.Extra-Charge__10]))