
1. Re: How to prevent the subtotals from having an INDEX() value
Ashalaxmi Adhya May 29, 2018 1:27 PM (in response to 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 May 31, 2018 1:00 AM (in response to Ashalaxmi Adhya)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 May 31, 2018 6:01 AM (in response to Prashant Sharma)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 1^{st} subcategory, middle slider to the 2^{nd} and last to the 3^{rd} 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. ( 2^{nd} 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 painfree workaround!
Much thanks!!!!

4. Re: How to prevent the subtotals from having an INDEX() value
Mavis Liu May 31, 2018 6:28 AM (in response to Ashalaxmi Adhya)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 May 31, 2018 11:48 PM (in response to Mavis Liu)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 May 31, 2018 11:56 PM (in response to Ashalaxmi Adhya)Hi Asha,
Here you go:
First I have created a subtotal flag as we need to find whether the row is a subtotal or not and for that I have used following calculation :
“Grand Total Flag”
TOTAL(MIN([SubCategory]))!=TOTAL(MAX([SubCategory]))
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 SubCategory & one for Subtotals (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 SubTotal flag is True and to check index for subtotal for first partition
ELSEIF [Index SubCategory]<=4 and not([Grand Total Flag]) then SUM([Quantity])+[P1]*SUM([Quantity])
//To Check if SubTotal flag is False and to check index for subcategory for first partition
//Partition 2
ELSEIF
([Grand Total Flag]) and [Index Subtotal ]=2 THEN SUM([Quantity])+[P2]*SUM([Quantity])
ELSEIF [Index SubCategory]>4 and [Index SubCategory] <=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 SubCategory]>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

Index() in Subtotals_PS.twbx 1.3 MB


7. Re: How to prevent the subtotals from having an INDEX() value
Ashalaxmi Adhya Jun 1, 2018 4:13 AM (in response to Prashant Sharma)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 Jun 1, 2018 7:46 PM (in response to Ashalaxmi Adhya)Glad it helped!
Thanks & Regards,
Prashant

9. Re: How to prevent the subtotals from having an INDEX() value
kavi ram Jul 19, 2018 7:03 AM (in response to Prashant Sharma)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