1 2 Previous Next 21 Replies Latest reply on Dec 2, 2019 7:22 PM by Soumitra Godbole

# Grand total not appearing when multiplying two calculated values

Hello,

I got stuck with my analysis in finding grand total when multiplying two calculated values.

A little information about my report. I have AMOUNT and VOLUME by month and year. First I Calculated "COST_PER_CASE" by dividing AMOUNT by VOLUME. Then I calculated difference between COST_PER_CASE of current year month vs last year month by using lookup formula (formula mentioned below). After doing this calculation, I wanted to calculate SAVING based on DIFFERENCE X VOLUME of current year. I got the correct result for each month but Grand Total is not coming.

Difference Formula : ZN(SUM([COST_PER_CASE])) - LOOKUP(ZN(SUM([COST_PER_CASE]))

Any help and/or advice would be highly appreciated.

Thank you,

Sameer

• ###### 1. Re: Grand total not appearing when multiplying two calculated values

Are you saying you want the total of the monthly values in the grand total column?

• ###### 2. Re: Grand total not appearing when multiplying two calculated values

And it's going to be tough showing you how to do what you're going to need to do.  You are still on version 9.0.  I don't have that installed.  Whatever I do to show you in your workbook, it's going to be upgraded to the latest version I have -- 10.1.

• ###### 3. Re: Grand total not appearing when multiplying two calculated values

HI Joe - Thank you for viewing my post. Yes I need total of monthly values in Grand total column.

I also have version 10 installed on my local machine so if you will provide me solution in upgraded workbook, that will work for me. Also, please let me know if you need another workbook in version 10. Looking forward to hear from you.

• ###### 4. Re: Grand total not appearing when multiplying two calculated values

Hi Sameer,

Here is a very simple solution to your problem using LOD Calculations. I have attached the twbx

file below and hope it answers your question i.e. gives you the Grand Total value (as shown below)

All it needed was 3 calculated fields as shown below:

This was your original formula (well rounded ) which I have left intact

Calculate the difference in Cost Per Case for each facility & month with prev year.

Also note that it is 2018 - 2019 as we have a cost saving (i.e. cost decreased)

You need to be careful when multiplying with Volume for the current year (i.e. year

shown in the filter) and do not do any aggregation as it is a product of two values.

Let me know if you have any questions or need further clarity. Also the twbx file was saved

in version 10.2 (downgraded). Best Wishes !

Sincerely,

Soumitra

2 of 2 people found this helpful
• ###### 5. Re: Grand total not appearing when multiplying two calculated values

Oops ! For some reason the twbx file did not get attached in the earlier post.

This way others with a similar problem will easily be able to access this solution.

Best Wishes !

Sincerely,

Soumitra

1 of 1 people found this helpful
• ###### 6. Re: Grand total not appearing when multiplying two calculated values

Tableau Solution in a Song

Greetings community,

Guess what ! I don't know what was so special about today (apart from being the 1st day of December

2019) but there seems to be something in the air from which inspired me to actually write song lyrics

that explain the methodology used to arrive at the solution (already posted earlier along with the twbx file).

Grand total not appearing when multiplying two calculated values

The song was a hit from 1984 called "Hello" by Lionel Richie. The song I came up with is called

"Tableau, LOD you're looking for ?" to encourage more folks to use LOD calculations. I wish I could

add music along with this. For those of you who read or play by ear, this would be a fun way to enjoy

a Sunday.

This is absolutely the first time that I have been able to add words to an already existing old tune while

solving a Tableau problem and using words that relate to the solution. It was definitely an inspiration that

connected music to words. Also here are the lyrics only to the above song:

I dedicate this solution and song (sung to the tune of Hello by Lionel Richie) to fellow colleagues

from Tableau Community who I have gotten to know during these 6 months who include:

Hari Ankem

Don Wise

swaroop.gantela

Ciara Brennan

Deepak Rai

Michael Gillespie

Joshua Milligan

Jonathan Drummey

Diego Parker

Amanda Boyle

Heyward Drummond

Yuriy Fal

Subodh Gupta

And all the others not listed above. Thanks for everything you have done so far and are continuously striving for. Best Wishes !

Sincerely,

Soumitra

5 of 5 people found this helpful
• ###### 7. Re: Grand total not appearing when multiplying two calculated values

Hi Soumitra! This is great stuff, love the creativity.

If you have Twitter, you should definitely share it there with the #datafam

Best,

Diego

1 of 1 people found this helpful
• ###### 8. Re: Grand total not appearing when multiplying two calculated values

Gracias Diego ! What would be really cool is giving an instrumental recital of

this in the form of background music with animated lyrics that people can

actually sing to.

Another option would be to just use the instrumental part (audio) in an animation

that shows the steps to solving the earlier problem. That does sound like a fun

weekend activity. Best Wishes !

Sincerely,

Soumitra

• ###### 9. Re: Grand total not appearing when multiplying two calculated values

Hi Soumitra - Thank you for looking into my problem and providing the solution. Your solution did provided my answer. But in your Difference (LOD) formula how can I add more years like past and future years.

Thank you for all your help,

Sameer

1 of 1 people found this helpful
• ###### 10. Re: Grand total not appearing when multiplying two calculated values

Soumitra -- Definitely something here!!  I'm a big fan of Weird Al style rewrites of songs, tailored to a specific subject matter.

I'm not sure how to propose this, but wouldn't it be great for some Tableau Community group of people to create some content to be performed at the next Tableau Conference.  Maybe they could be called the "Tableau Forum Madrigals".  Or the Tableau Barbershop Quarter.  Create a couple of these songs,  Do a clever show to be performed during one of the morning keynotes.  Have a number that laments a hot wish-list item from the Ideas Forum.  (In the past it could have been about Dynamic Parameters, for example.)  And maybe do one about Salesforce.  (By next year it will be clear what direction Salesforce will be going with the acquisition of Tableau.)

Out of this great big forum there has to be plenty of talent to write the pieces, and other talent interested in performing them.  I would volunteer!

2 of 2 people found this helpful
• ###### 11. Re: Grand total not appearing when multiplying two calculated values

This is great, thanks for a perfect Monday morning post! Especially like the call out in the lyrics to mark correct or helpful.

Nick

1 of 1 people found this helpful
• ###### 12. Re: Grand total not appearing when multiplying two calculated values

Hi Joe,

"Welcome to the party Pal". Funny how our paths never crossed these six months. I do

remember seeing your cool solutions though the most memorable thing I remember about

you is your avatar or logo with a slice of Pepperoni which always reminded me it was time

to have a bite !

So you're also a huge fan of the legendary "Weird Al". Superb ! He is truly legendary and

to think that one can play Rock & Roll on an Accordion is phenomenal.

The greatest thing about his songs are they are not at all offensive but funny as **** and

makes you laugh a lot till your stomach hurts ...... . Here are my top 10 favorites with

the parody in bold and the original underlined inside the parentheses.

1) Rye or the Kaiser (Eye of the Tiger): I still crack up remembering my friend's deli and the

time I would order a Tuna sandwich.

2) Lasagna (La Bamba): The Accordions & Mandolins instrumentals really rock

3) The Saga Begins (American Pie): You have to be a genius to be able to use the

word  "Midi-clorian" in a song.

4) Eat it (Beat it): My favorite since childhood even though I did not eat Raisin Bran back then

5) Like a Surgeon (Like a Virgin): The video is just hilarious with the Lion (from the original video)

and the waiver form he makes the patient sign and the lyrics

"I can hear your heartbeat for the very last time"  .

6) Girl's Just Wanna Have Lunch (Girl's Just Wanna Have Fun):

7) Amish Paradise (Gangster Paradise): The video is brilliant and hilarious like all his other ones.

8) I Love Rocky Road (I Love Rock & Roll):

10) Beverly Hillbillies (Money for Nothing):

He should have got several Grammies for his contribution, ingenuity and helping mankind (by cracking

them up with laughter and making people forget their worries). I am truly delighted to meet another fan of

such a great artist. Hope there are many more (or at least  any more )

Regarding next year, if all else (world, solar system, galaxy ... universe and myself) stays the same, you

can certainly count me in as I will definitely come up with something crazy that will be helpful and fun. Your

suggestions are great especially the "Tableau Barbershop Quarter" as I play a few ethnic (European) string

instruments (though not all at the same time )

Also thanks for mentioning about the company Salesforce ! I only hope Tableau has not lost it's name

or I may lose my mind (). Oops I see I have a question regarding a solution and gotta go, but it sure

was fun and thank you once again for these wonderful moments. This is truly a "Whole New World" (Not

the song from Disney's  Aladdin). Best Wishes !

Sincerely,

Soumitra

• ###### 13. Re: Grand total not appearing when multiplying two calculated values

Thanks Nick ! It came to me in the Nick of Time . It has been truly a wonderful knowing

all of you and thanks for your brilliant ideas, solutions and having these fun moments every

now and then. Truly priceless !

Sincerely,

Soumitra

• ###### 14. Re: Grand total not appearing when multiplying two calculated values

Hi Sameer,

Sorry I got so carried away having such a wonderful time meeting folks who

are fans of my all time favorite singer/comedian/lyricist "Weird Al Yankovic".

Coming back to Tableau, sure you can add more years. There are several

ways and the popular way is using Parameters. 2 would be ideal as one would

be for the Given Year (say 2019) and the other a number value of how many

years back or forward (for comparison) ie in your problem we used 2019 as the

current year and it was compared with a year ago 1.

Let me know if this works and I will be glad to weave out a solution using the

same workbook. In case you have a specific requirement, I would be glad to work

on that as well and only hope it is doable.

Awaiting your reply and I assure you that I  won't be creating any new lyrics as

it's "Just another Manic Monday" (by The Bangles).

Sincerely,

Soumitra

1 of 1 people found this helpful
1 2 Previous Next