14 Replies Latest reply on Oct 7, 2019 8:46 PM by Nitin Joshi

# Custom Grand Totals

Hello All,

i've been trying with custom grand total but unable to get it.

manaed to get sum of measure in grand total based on a perticular dimension but unable to get last value for last date in grand total.

I want sum for all dates in for one domension and last date value in grand total.

below is image what i mean. Also attached tableau worksheet

• ###### 1. Re: Custom Grand Totals

Nitin,

You can try

if first() = last() then

if  attr([Unit])='AB' and attr([Partucilars])='DAILY_STOCK'

then  MIN({FIXED [Unit],[Partucilars],[Product],[UOM]: SUM( if [Trx Date] = {MAX([Trx Date])} then [daily Total] end)})

else SUM([daily Total]) end

else SUM([daily Total])

end

I cant save in version 9.3

Michel

1 of 1 people found this helpful
• ###### 2. Re: Custom Grand Totals

Hi,

Please find the below attachment and screenshot, If it's working then marked as correct and helpful.

Thanks,

Amar D. Savale

• ###### 3. Re: Custom Grand Totals

The same formula when i put in my actual sheet, it is not working. Grand total is blank for stock.

I'm comput using pane accross down. I change to Unit,Particulars,Product, UOM but not working.

in Formula it is MIN only. I just tried with MAX.

• ###### 4. Re: Custom Grand Totals

First you need to compute table accross.  first() and last() must compute on each date, both will equal 0 for the Total and only for the Title.

For the lod, you can use either MIN,MAX or AVG but not SUM.

And in your new image, you have a different dimension on the row,   so you need to replace  Particulars  with   Pariculars-CF  in the  lod.

1 of 1 people found this helpful
• ###### 5. Re: Custom Grand Totals

Thanks. I did change everything according to your suggetions but now it shows the summation of the all dimension values.

below is the image of my formula.

• ###### 6. Re: Custom Grand Totals

Nitin,

I would need to see your workbook to troubleshoot more.

Make sure there is no leading or trailing space  in the word  'STOCK'.

You could try  if attr(trim(Unit)) = 'IIL  and attr(trim(Particulars-CF)) = 'STOCK'

• ###### 7. Re: Custom Grand Totals

Thank you. Attached is the Latest tableau version file.

• ###### 8. Re: Custom Grand Totals

Nitin,

I can open you workbook but i don`t see the data because it is trying to connect to your data source.  You will need to extract the data first.

Also make sure that Grand Total computes using Table Across,  you have it using  Table Across then down.

Michel

• ###### 9. Re: Custom Grand Totals

Thanks Michel for the Follow up. I did try Compute using Table Across and almost all options including Table Across Then down but could not get the desired result.

Anyway attached is the tableau sheet with Extract for you reference.

• ###### 10. Re: Custom Grand Totals

Nitin,

Here is a screenshot of the Book1.twbx  you just uploaded,  without any modification.

For the Grand Total of STOCK  it returns the  last day value (856,986)  otherwise it returns the real total of the row.

Isn`t what you are looking for ?   If not,  please rephrase your requirement.

• ###### 11. Re: Custom Grand Totals

Hello Michel,

first of all apologies for replaying so late.

Yes , you mentioned correctly, using extract it shows the output i wanted but when i go back to live data, it grand total is blank. It seems there is data issue. I mean if i select 2 perticular units(which i wanted and another) then the grand total is ok. If i select unit which i wanted and 3-4 other units, grand total is wrong.

I'm attaching extract for you for 3 units. with 2 units, total is ok but with 3rd one data is wrong.

also attaching a video file showing what i said above.

Thanks.

• ###### 12. Re: Custom Grand Totals

Nitin,

In your Grand Total calculation, you have a validation on the Unit Name

if  attr(trim([Unit Name]))='INDIIL'  and attr(trim([Daily_Categ]))='STOCK'

If you want to return the last day value only for  Daily_Categ = STOCK  for all Units, just replace it with

if   attr(trim([Daily_Categ]))='STOCK'

To troubleshoot, I would need the .twbx,  not just the  hyper with empty worksheets.

• ###### 13. Re: Custom Grand Totals

After a second look,

You probably need to replace

{MAX([Trx Date])}

with

{FIXED [Unit Name]: MAX([Trx Date])}

I guess the Max date may  be different for every Unit Name,  and since you now have more than one in your dataset  it could be the cause of the problem.

• ###### 14. Re: Custom Grand Totals

Thank You Michel.

{FIXED [Unit Name]: MAX([Trx Date])} This Fixed in Sum Fixed my problem.

Thank you once again.