9 Replies Latest reply on Jul 19, 2018 7:03 AM by kavi ram

    How to prevent the subtotals from having an INDEX() value

    Ashalaxmi Adhya

      Hi All! This is my first question here.

       

      I am using INDEX() in a calculation with a parameter ( similar to this idea http://visualbi.com/blogs/data-discovery/analysis-tableau-using-dynamic-parameter-linkage/ )

      ex: when index() <=4, then sales * Parameter 1

      when index () <=8 then sale * Parameter 2  ...

       

      However, I have category and then 4 subcategory each ( thus the <=4, <=8 etc). When i subtotal the subtotal rows also get an Index values of their own. First subtotal is 1, second 2 etc... This is messing up the calculations.

       

      Any idea how to prevent it from indexing or how to index without considering the row, something with the logic of :

       

      if row name = 'total' then 0, else index()

       

      Thanks a ton!

       

      P:S - cant share data as it's confidential, but if needed , i can recreate with dummy data.

       

      Message was edited by: Ashalaxmi Adhya , added sample data workbook to illustrate problem

        • 1. Re: How to prevent the subtotals from having an INDEX() value
          Ashalaxmi Adhya

          Prashant Sharma Would you be able to point me towards a solution please ?

          • 2. Re: How to prevent the subtotals from having an INDEX() value
            Prashant Sharma

            Hi Ashalaxmi,

             

            Welcome to Tableau Community!

             

            Can you please share the workbook with dummy data set. Also, give more brief about the problem statement  and explain it with this dummy data set.

             

            Thanks & Regards,

            Prashant

            • 3. Re: How to prevent the subtotals from having an INDEX() value
              Ashalaxmi Adhya

              HI Prashant Sharma ! Thanks a ton for replying!

               

              The attached workbook has the concept/problem recreated using superstore data.

               

              What I am trying to do : Increase /decrease quantity of sales to see how sales $ will get affected. Basically slide the parameter by different degrees ( - 50% to +50 % ) to see the % change in sales

               

              How I am doing it : I have linked index to parameters, so that when they are arranged side by side in the dashboard, the top slider corresponds to the 1st  subcategory, middle slider to the 2nd and last to the 3rd subcategory. ( !st Dashboard coloured Green)

               

              Formula used to find change in Sales qty –

               

              IF INDEX() <=4 THEN SUM([Quantity])+[P1]*SUM([Quantity])

              ELSEIF INDEX() >4 and INDEX() <=13 THEN SUM([Quantity])+[P2]*SUM([Quantity])

              ELSE  SUM([Quantity])+[P3]*SUM([Quantity])

              END

               

              Formula used to find change in Sales $ -

               

              IF [Change of Qty Sold]= SUM([Quantity]) THEN SUM([Sales])

              ELSEIF  INDEX()<=4 then (SUM([Sales]) + SUM([Sales])*([P1]*SQUARE(CORR([Sales],[Quantity]))))

              ELSEIF INDEX()>4 AND INDEX()<=13 then ((SUM([Sales]) + SUM([Sales])*[P2]*SQUARE(CORR([Sales],[Quantity]))))

              ELSE ((SUM([Sales]) + SUM([Sales])*[P3]*SQUARE(CORR([Sales],[Quantity]))))

               

              END     

               

              Problem : Since I am using INDEX() in my calculation to relate it to the corresponding parameters ( P1,P2,P3) , it is going for a toss the moment I choose to “Show all Subtotals”. This is because each subtotal is getting its own index, and then changing asper the paprameter value in the formula and not as a totaling of the subcategory above. ( 2nd Dashboard coloured Red)

               

              Is there any way I can do the projections based on the parameters, but not dependent on index ? ( I don’t want to hardcode subcategory names as then I can’t replicate for other sets , eg product type, customer name). It looks like I cant prevent the subtotals from getting an index number, so was wondering if there is a relatively pain-free workaround!

               

              Much thanks!!!! 

              • 4. Re: How to prevent the subtotals from having an INDEX() value
                Mavis Liu

                Hi Ashalaxmi,

                 

                Have you ever used the size() function before? This calculation works out the size of the partition, in this case, subtotal will always =1.

                 

                 

                Maybe you can create a nested if/case statement which means it only uses looks at the index() values when size()>1?

                 

                Thanks,

                 

                Mavis

                • 5. Re: How to prevent the subtotals from having an INDEX() value
                  Ashalaxmi Adhya

                  Hi Mavis Liu ! Thank you for the tip. I modified the formula for Change in Qty Sold to -

                   

                  IF INDEX() <=4 and SIZE()<>3  THEN SUM([Quantity])+[P1]*SUM([Quantity])

                  ELSEIF INDEX() >4 and INDEX() <=13 and SIZE()<>3 THEN SUM([Quantity])+[P2]*SUM([Quantity])

                  ELSEIF INDEX() >13 and SIZE()<>3 THEN SUM([Quantity])+[P3]*SUM([Quantity])

                  END

                   

                  But obviously I am missing something here as the subtotals values became blank!

                   

                  • 6. Re: How to prevent the subtotals from having an INDEX() value
                    Prashant Sharma

                    Hi Asha,

                     

                    Here you go:

                     

                    First I have created a sub-total flag as we need to find whether the row is a sub-total or not and for that I have used following calculation :

                    “Grand Total Flag”

                    TOTAL(MIN([Sub-Category]))!=TOTAL(MAX([Sub-Category]))

                    Setting that to Compute Using Pane (Down) makes it work and to check I have created following calculation that I have used in sheet.

                    IF [Grand Total Flag] THEN 1 ELSE 0 END

                    After that I have created 2 Index calculations, One which provides indexing for Sub-Category & one for Sub-totals (Both computed on Table(Down).

                     

                    Now the final part of calculation, explanation is in comment :

                     

                    IF

                    //Partition 1

                    ([Grand Total Flag]) and [Index Subtotal ]=1 THEN SUM([Quantity])+[P1]*SUM([Quantity])                                                

                    //To Check if Sub-Total flag is True and to check index for subtotal for first partition

                     

                    ELSEIF [Index Sub-Category]<=4  and not([Grand Total Flag]) then SUM([Quantity])+[P1]*SUM([Quantity]) 

                    //To Check if Sub-Total flag is False and to check index for sub-category for first partition

                     

                    //Partition 2

                    ELSEIF

                    ([Grand Total Flag]) and [Index Subtotal ]=2 THEN SUM([Quantity])+[P2]*SUM([Quantity])

                    ELSEIF [Index Sub-Category]>4 and [Index Sub-Category] <=13 and not([Grand Total Flag]) then SUM([Quantity])+[P2]*SUM([Quantity])

                     

                    //Partition 3

                    ELSEIF

                    ([Grand Total Flag]) and [Index Subtotal ]=3 THEN SUM([Quantity])+[P3]*SUM([Quantity])

                    ELSEIF [Index Sub-Category]>13  and not([Grand Total Flag]) then SUM([Quantity])+[P3]*SUM([Quantity])

                     

                    END

                     

                    You can find the solution in attached file. I have done this only for “Change in Qty Sold”. You can replicate the same logic for other KPIs.

                     

                    Thanks & Regards,

                    Prashant

                    • 7. Re: How to prevent the subtotals from having an INDEX() value
                      Ashalaxmi Adhya

                      Hi Prashant Sharma Thank you so much for this ! Really appreciated!! You saved me so many work hours ( and possibly days! )

                      • 8. Re: How to prevent the subtotals from having an INDEX() value
                        Prashant Sharma

                        Glad it helped!

                         

                        Thanks & Regards,

                        Prashant

                        • 9. Re: How to prevent the subtotals from having an INDEX() value
                          kavi ram

                          Hi Prashant,

                           

                          I ma facing some issue for Fiscal year change for particular regions and i have posted the question in forum,

                          Please find the details in the below thread link,

                           

                          Thanks,

                          Kavi

                           

                          https://community.tableau.com/thread/276196