1 2 Previous Next 16 Replies Latest reply on Sep 8, 2017 9:49 AM by Minnu Meena

    Grand Total Issue

    Minnu Meena

      Hello All,

       

      I have created a formula:

      Formula.PNG

      After creating the formula i got correct values but grand totals are coming wrong.

      Grans total 1.PNG

      But the total was wrong. I should get 4.78 instead of 4.97.

       

      Grand total 3.png

       

      When i am selecting total using sum  then i got  correct total.

       

      grand total 2.png

       

      How can i get correct  grand total automatically. I have to use this total value in another calculation. So how i can i achieve this?

      Please help me out. I don't have a workbook to attach. Thanks in advance.

       

       

      Thanks & Regards,

      Minnu.

        • 1. Re: Grand Total Issue
          Deepak Rai

          You can use Window_SUM , if you need to use it in other calculations.

          • 2. Re: Grand Total Issue
            Minnu Meena

            If i am applying Window_Sum, calculation it self getting wrong value so how i use this into another cal. ?

            • 3. Re: Grand Total Issue
              Minnu Meena

              Hello,

               

              Pooja Gandhi

              Simon Runc

               

              Sorry for mentioning you both, but i don't have any option. Please help me with your solution for my query. Thank you in advance.

               

              Regards,

              Minnu.

              • 4. Re: Grand Total Issue
                Simon Runc

                hi Minnu,

                 

                So using a WINDOW_SUM (with Category in the VizLoD) on your calculation, and then running the WINDOW_SUM by Category should give you the right result, but you'd need to have Category in the VizLoD (viz Level of Detail) whenever you used it. You can probably create a FIXED LoD, which would allow you to calculate this "Total" number "off canvas" (meaning that the result will be independent of your VizLoD)

                 

                How we'd build the FIXED LoD will depend on the Level of Detail the calculation is run at. So although you've got Category in your current view, I can see you have a filter on a single SOL_ID, so if you want this calculated by SOL_ID/Category you'd need to include both in the FIXED LoD.

                 

                It's hard to be exact without seeing a workbook, are you able to post an anonymised version? (it can just be a small subset of the current data, with at least 2+ SOL_IDs, and any other dimensions that are needed in the grain of this calculation). I'll then be happy to take a look.

                • 5. Re: Grand Total Issue
                  Minnu Meena

                  Hello Simon,

                   

                  Thank you so much for responding.

                   

                  Actually my issue was not getting correct total when i am applying the sol_id filter.

                  Normal calculated field giving correct value. when ever applying a category or ant dimension then it is giving wrong value. I have applied windows_sum,fixed aswell but still it was giving wrong value. I need corrcet value because i have to use that cal field total as reference line as month level. so the reference line value as well giving wrong value.

                   

                  Even I have tried with the current dummy data there it was giving correct total but when applying in main dashboard level values giving wrong.

                   

                  I am attaching the workbook with the relevant data. Kindly Plz go through it.

                   

                  Thanks & Regards,

                  Minnu.

                  • 6. Re: Grand Total Issue
                    Simon Runc

                    Thanks for the workbook...

                     

                    So is the 43.66 value correct here?

                     

                     

                    And if so do you want this to be the same for every category and every SOL_ID

                     

                    Currently if I filter to SOL_ID = 003 for example I get a different value

                     

                    • 7. Re: Grand Total Issue
                      Minnu Meena

                      Yeah for now in that data grand totals coming correct only. Any how its a dummy data.

                       

                      Thank yo so much Simon for working. Any way i will try to get a data related to this query by tomorrow. Plz help me  if possible.

                      • 8. Re: Grand Total Issue
                        Simon Runc

                        Hopefully last question....

                         

                        So is the 43.7 (well 43.66) figure correct because it's the MIN of the 3 SOL_IDs we have in this (dummy) data? It's not that only SOL_ID = 2 that determines the correct value?

                         

                        • 9. Re: Grand Total Issue
                          Minnu Meena

                          No Actually in my data i have more than 2000 SOLS.

                          So after applying the filter the actual total and getting total differeence was like

                          eg: My actual total should be :43.35

                                 Me getting total was :  43.60 .

                           

                          The difference  was nearly 30 to 40 . So in this way for every filter giving me wrong value.

                          • 10. Re: Grand Total Issue
                            Simon Runc

                            So in this (dummy) example you've said the correct value is 43.66...this number is only returned by your calculation for SOL_ID = 002. I'm trying to determine why SOL_ID = 002 gives the correct value. If it is that a particular SOL_ID (in this case 002) gives the right result, we can encode this rule and return the 43.66 to any SOL_ID (and so in your real world example, if there is a particular SOL_ID that gives the correct result, you can just change my formula to use that one instead), but if SOL_ID = 002 gives the correct result because (of the 3 examples in the dummy data) this one is the lowest value, I can use that rule instead. I've (of course) no idea what SOL_IDs are!!

                            • 11. Re: Grand Total Issue
                              Minnu Meena

                              Hello Simon,

                               

                              Here i have attached the workbook with the exact data.

                               

                              Hope now you can understand my problem.  

                               

                              When i am applying the filter then the values were getting wrong. Plz help me.

                               

                               

                              Thanks & Regards,

                              Minnu.

                              • 12. Re: Grand Total Issue
                                Simon Runc

                                Just to check...

                                 

                                So the value in the Grand Total here...1.161 is the correct one we are aiming for?

                                 

                                 

                                If so, what is the reason that it's correct when SOL_ID = 20? If it's just because that SOL_ID (whatever a SOL_ID is!) 20 is "special" in someway, that's fine (I can encode the formula so it always returns the Grand Total value for SOL_ID=20 to all SOL_IDs), but what to check there isn't a deeper reason.

                                • 13. Re: Grand Total Issue
                                  Minnu Meena

                                  When sol id =20 grand total getting 6.13. This was wrong, actual value is 6.00

                                  The same way sol id =60  total getting was 4.97 but actual should get was 4.79.

                                  If we are selecting individual values after applying sol filter category level the total was correct. After applying grand total it is showing wrong.

                                   

                                  For eg: If SOL ID=8

                                   

                                  Electricity=1.11

                                  Postage=0.93

                                  Printing stationery=1.20

                                  Repair and maintenance = 5.80

                                  Travelling= 3.41

                                   

                                  If normally we are adding these 1.11+0.93+1.20+5.80+3.41=12.44 is correct.

                                  But in the grand total we are getting 12.60. This is not correct

                                  Like this for every SOL_ID value was giving wrong.

                                  So i am assuming in this way.

                                   

                                  Actually SOL_ID its nothing but Branch ID'S.

                                  • 14. Re: Grand Total Issue
                                    Simon Runc

                                    OK I think I see....

                                     

                                    I've used this LoD...

                                     

                                    [Revised Monthly Limit - LoD]

                                    {FIXED [Sol Id]: SUM({FIXED [Sol Id], [Category]: [Revised Monthly Limit]})}

                                     

                                    and now I get the numbers you want, without needing Category in the VizLoD (or changing how the Grand Totals compute)

                                     

                                    so this works out the  [Revised Monthly Limit] at Sol_Id/Category Level, and does a SUM of these values by Sol_Id

                                     

                                     

                                    Hope that does the trick

                                    1 2 Previous Next