8 Replies Latest reply on Feb 24, 2016 5:58 AM by Simon Runc

    Calculation Help

    Manas Tripathi

      Below is the extracted twbx file where i need to calculate the average for incoming volume and outgoing volume.

      I have used a zn(lookup()) for both and after it when i try to find the average using grand total , the valule of grand total remains same.

      I have been trying a lot but could not get enough resources online.

      I am attaching the snapshot of the requirement.

      Kindly have a look and suggest .

       

      Regards,

      Manas

        • 1. Re: Calculation Help
          Manas Tripathi

          Can anyone please have a look ?

          • 2. Re: Calculation Help
            Simon Runc

            hi Manas,

             

            So grand totals in Tableau are actually calculated at the 'Grain' (or Level of Detail) one above the Viz..and as such you do get these situations when using Table Calcs (as the Table Calc for the Grand Total is being run at a different level of Detail from the main values)...Can I ask why you are using Lookup? (is it purely to populate the blanks with zeros?

             

            If I remove the LOOKUP part...I get the following

             

            Once I've switched the Calculate Totals to Average

             

            One other reason you may have been using LOOKUP is that you want the NULLs to be zeros in the Average calculation? As you can see here the NULL values aren't included as zeros in the Average Calc (so for 25th Jan Incoming is 1.5 ((1+2)/2) and not 0.375 is we have evaluated the NULLs are zero.

             

            Hope that helps.

            • 3. Re: Calculation Help
              Manas Tripathi

              Hi Simon,

              Thanks for a quick reply.

              Yes, lookup is used only to populate blank values with 0 . (which was mandatory as per requirement). also , grand total has to be on row level. is it possible to find avg on row level and then populate the blanks with 0 ?

              regards,

              manas

              • 4. Re: Calculation Help
                Simon Runc

                Yes i'm pretty sure we can do that...Just so I'm clear, would you want the average for the example I gave above to be 1.5 or 0.375? Depending how you want it calculated will  change the solution.

                • 5. Re: Calculation Help
                  Manas Tripathi

                  Hi Simon,

                  I am attaching a screen shot which will make it clear . so i have picked start date as 1st dec, 2015 and end date is 6th dec, 2015. the data output (column pill DAY(day) shows all days except 5th dec , as there is no data on that day). lets now look at time of day (row pill) and lets focus on 10:01-11:00 . it has been divided into incoming volume and outgoing volume. so average of incoming volume will be 2+0+0+0+0 (row level ) divided by no. of days. similarly outgoing volume = 3+0+0+0 (row level) . similarly average for this will be calculated. and these averages should appear at row level for 10:01-11:00.

                   

                  Hope this makes it clear.

                  Regards,

                  Manas

                  • 6. Re: Calculation Help
                    Simon Runc

                    Yes that makes sense...I thought it was the Column Grand Totals were we after!!

                     

                    So the reason LOOKUP populated the NULLs (blanks) with Zeros is that it is a table calculation, and when we have 2 opposing blue pills and a table calc, Tableau envokes 'Densification'...This is a very complicated and not well documented feature (I'm not even sure it's deliberate!!). If you are interested Joe Mako has done some research into when/why it occurs and done this excellent video Tableau Request Live - Data Densification on Vimeo

                     

                    As such I can keep the measures as Non-LookUps, and use an  index() calculated field (also a Table Calc) to trigger the densification.

                     

                    As you can see from the below I have it working

                    However this is in Tableau 9.0, and when I try and replicate this in 8.2 (which is the file you sent through) it doesn't do the same thing (as I said I think densification is 'side-affect' and so as they change Tableau from version to version it looks like this has been affected). I can post the 9.0 workbook for you if that'll help?...but I'll have a bit more of a play with the 8.2 version and see if I can get it doing the same thing!

                    1 of 1 people found this helpful
                    • 7. Re: Calculation Help
                      Manas Tripathi

                      Nice work, appreciate it Simon.

                      Can you send me the 9.0 report and let me have a look at it.
                      If you could figure out a way in 8.2, it would be beautiful.

                      Regards,

                      Manas

                      • 8. Re: Calculation Help
                        Simon Runc

                        ...welcome to the murky world of 'Data Densification'!!! - a very useful and frustrating feature, in equal measure!!

                         

                        attached is the 9.0 version...I'll have a bit more of a play with the 8.2 version and let you know if/when I get to a solution!