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

    Sameer Sharma

      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.

       

      Attached please find twdx file.

       

      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
          Joe Oppelt

          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
            Joe Oppelt

            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
              Sameer Sharma

              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.

               

              Thank you in advance. 

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

                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)

                 

                Visual.png

                 

                All it needed was 3 calculated fields as shown below:

                 

                Formula 1.png

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

                 

                 

                Formula 2.png

                    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)

                 

                 

                Formula 3.png

                    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
                  Soumitra Godbole

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

                  Anyway here it is now and hope this is helpful. In case this answers your problem,

                  then please mark as helpful, correct answer to close the thread.

                   

                  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
                    Soumitra Godbole

                                                          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.

                     

                    Song - Fake Book.png

                     

                    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:

                     

                    Untitled.png

                     

                    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

                    Veronica Simoes

                    Michael Gillespie

                    Joshua Milligan

                    Jonathan Drummey

                    Diego Parker

                    Amanda Boyle

                    Heyward Drummond

                    Yuriy Fal

                    Paul Wachtler

                    Nick Parsons

                    Manoj Lobo

                    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
                      Diego Parker

                      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
                        Soumitra Godbole

                        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
                          Sameer Sharma

                          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
                            Joe Oppelt

                            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
                              Nick Parsons

                              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
                                Soumitra Godbole

                                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):

                                 

                                9) Fat (Bad)

                                 

                                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
                                  Soumitra Godbole

                                  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
                                    Soumitra Godbole

                                    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