10 Replies Latest reply on Aug 28, 2018 9:11 AM by Sara Kim

    Why Grand Total For Lookup field is Blank

    Sara Kim

      Hello, I have created this view and within the workbook there is a Sales DoD LK column which is computing previous day sales.

       

      The lookup works fine, but the Grand Total for this column is showing blanks! I tried to change computing using, but not seeing any results.

       

      Why am I not able to get any grand totals for this columns?

       

      Also, Ive seen examples for customizing grand totals using IF First()=0, but in my view First() always start from the rows, how can I customize grand totals when I am calculating column grand totals in the below cross tab?

       

      Sales Dod LK: LOOKUP(SUM(Sales),-1)

       

      Attaching workbook here.

        • 1. Re: Why Grand Total For Lookup field is Blank
          Joe Oppelt

          (V 9.3 here)

           

          Sara -- I don't have 9.3 installed, but I have 9.2.  I created a similar example for you so you can open it with your installation.

           

          Tableau-generated totals get frustrating when you want to total table calcs.

           

          In the attached on Sheet 2 I have your sale lookup-1 calc.  I also added a calc to display the INDEX() that tableau sees when it's doing the totals.  You can see that tableau is handling the grand total line separately from the rest of the data rows.  It sees index=1.  (So the LOOKUP on the grand total row is trying to get index=0, which doesn't exist, so tableau gives you NULL.)

           

          Look at the calc for DISPLAY SUM OF SALES.  You can SOMETIMES hack into what tableau sees by testing for first-last.  In that calc I overrode the window_sum that tableau effectively does for the grand total, and I told tableau to display 999.  You can see that in the grand total slot for that calc.  But even trying to do that for a column that is a table calc, Tableau just doesn't want to do it.  (I wanted to insert the table calc that I created to get the window sum of the lookup column.)

           

          The first=last trick will come in handy for you some day for some other use, but today is not that day. 

           

          When it comes to displaying a grand total of a table calc, I create a second sheet that just displays the totals I want.  I turn off analysis-totals on my data sheet, and I just put the two sheets on a dashboard together to look like one viz.

           

          I made a copy od sheet 2 (see sheet 3) to remove the grand total and to mess with some formatting.

           

          On Dashboard 1 I placed sheet 3 and sheet 4.  Look at Sheet 4.  You will see INDEX in the filter shelf.  If you take that off you'll see that all the rows show up.  We need all the rows to get the calcs to work correctly.  The filter just displays the first of the rows.

           

          Right now the two sheets don't align well.  That's just a matter of formatting and artwork to make it look good on the dashboard.

           

           

          Also you'll notice that on Sheet 4 I shoved two text strings on ROWS.  This is just a matter of forcing shape and formatting onto the sheet.

           

          If you have questions, ping back.  I'm glad to help.

          • 2. Re: Why Grand Total For Lookup field is Blank
            Sara Kim

            Hello Joe,

             

            First of all, Thank you so much for responding and giving such a thorough details!

             

            I am taking a look at the details. I apologize to not include this lookup filter in the sheet. I need to filter out the data using parameter, but without filtering the entire data from view.

            That's why I am using this lookup filter = Date parameter highlighted below. How can I get the totals for Lookup -1 only for the date selected in the view.

             

            I am trying to find the result 3598 for Total of Lookup -1 calculated field

             

            I am attaching the workbook - I just added the lookup filter in the filter shelf. Thanks alot!

             

            • 3. Re: Why Grand Total For Lookup field is Blank
              Joe Oppelt

              When you use a table calc as a filter, it does NOT delete rows in the underlying table like a quick calc does.  So that's why you're still getting the total-total in those total calcs.

               

              (The reason for this is so that table calcs that need access to the full set of data -- such as a percent of total across all states even though you're just displaying Wyoming on the sheet.  In fact, that's what I'm relying on when I filter by INDEX on Sheet 4.  I need all rows to get the total-total, but I only want to display one row in the sheet.)

               

              So take a look at what I did with [total of LOOKUP -1].  I incorporated the Date Lookup Filter Boolean into the table calc.  If you want to do the same for [Window sum for Sum of sales] the same principle would apply.

              • 4. Re: Why Grand Total For Lookup field is Blank
                Sara Kim

                Hello Joe,

                 

                Thank  you so much! That did it for me! So many tricks to be learned when using tableau.

                 

                I am getting very close and not able to figure out on sorting. I need to sort Category by SUM of SALES in descending order. But since I have a lookup filter being applied, sorting is not working from the view

                 

                I am attaching the workbook. Thanks

                 

                 

                • 5. Re: Why Grand Total For Lookup field is Blank
                  Sara Kim

                  I think I got it working!

                   

                  I created this calculated field and added this before Category.

                   

                  RANK(IF [Date Lookup Filter] THEN SUM([Sales]) ELSE 0 END, 'desc')

                   

                  • 6. Re: Why Grand Total For Lookup field is Blank
                    Joe Oppelt

                    Looks like you're getting the hang of messing with table calcs!

                     

                    Now that the rank calc is at the front of the ROWS list, you probably don't actually want to see it in the viz.  So right click on the rank pill and uncheck "Show Header".  The pill remains where it is, and does what it does, but it no longer displays on the viz itself.

                    • 7. Re: Why Grand Total For Lookup field is Blank
                      Sara Kim

                      Hello Joe,

                       

                      Thanks yes, I was able to hide that column.

                       

                      I am sorry to bother you again, but I thought lookup would work fine when using -1, or -5 offset. But I just noticed in some cases in my dataset that some of the dates are missing so for example: If I select 2/9/2012 from the attached workbook within Furniture category.

                       

                      Previous business day: 2/8/2012

                      Previous Week day(business): 2/2/2012

                      Previous Year: 2/9/2011

                       

                      But since some of the dates are missing - if there is no dates 2/8/2012 or 2/2/2012 then I want to show 0 as the result instead. I have the calculations for previous day, week and year, but I need to only get the values if it exists, but since lookup -1, lookup - 5 just gets previous rows, this is not working for me Right now -5 lookup is getting the data for 1/28/2012 instead it should be for 2/2/2012.

                       

                      Is there any way we can check for the dates before getting the value, kind of like this below?

                       

                      LOOKUP(If previous week = Order Date THEN SUM(Sales) ELSE 0 END, -5)

                       

                      Please note - I was able to get this working using LOD's but that made the report supper slow. When trying to filter the report by date - it would take more than ~30 secs, since I needed to create multiple columns and nested LOD's as well.

                      Thats why I started to use the Lookups which is super fast (2 secs).  Please let me know if i can use lookup to get the desired result?

                       

                      Thanks in advanced! I really appreciate your help.

                      • 8. Re: Why Grand Total For Lookup field is Blank
                        Joe Oppelt

                        Sara Kim wrote:

                         

                        ...

                         

                        But since some of the dates are missing - if there is no dates 2/8/2012 or 2/2/2012 then I want to show 0 as the result instead. I have the calculations for previous day, week and year, but I need to only get the values if it exists, but since lookup -1, lookup - 5 just gets previous rows, this is not working for me Right now -5 lookup is getting the data for 1/28/2012 instead it should be for 2/2/2012.

                         

                        Is there any way we can check for the dates before getting the value, kind of like this below?

                         

                        LOOKUP(If previous week = Order Date THEN SUM(Sales) ELSE 0 END, -5)

                         

                        LOOKUP is totally an index-driven animal.  -5 means "current index minus 5", and whatever data falls there, that's what you get.

                         

                        This is just a wild idea, but it might work.

                         

                        WINDOW_SUM( (if previous week = ATTR(Order Date) then SUM(sales) END), -5 , 1)

                         

                        If you don't give a start and end on WINDOW_SUM it sums up the whole series, but you have the option of giving those.  So what I did above was to sum up from current location through 5 prior slots.  And we can imbed IF logic in there, so that will tell the window sum to add only those rows where the date is what we want.

                         

                        If there is no such date in the series, then it will return null, so you can wrap that whole thing in ZN( WINDOW_SUM( ... )) and the result will be zero if the inner workings return null.

                        • 9. Re: Why Grand Total For Lookup field is Blank
                          Joe Oppelt

                          OK, So I hacked this up in my 9.2 example.

                           

                          See [One Day Ago] and [Seven Days Ago].

                           

                          I replicated your [Previous Day] and [Previous Week] calcs and used them.  Right now I have selected 2/9/2011 in [Date].

                           

                          I added the ZN function in [Seven Days Ago] to force the zero.  Edit it out and you'll get nothing.

                           

                          Select 1/27/2011 and you'll get a value in [Seven...] but nothing in [One ...]

                           

                          Select 1/21/2011 and you'll get values for both.

                           

                          In [Seven ...] I had to add an extra layer of IF logic to calc only on the selected [Date].  Take it off and you'll see 880 get populated across 8 rows.  I didn't need it for [One...] because it just looks at one mark in the series.

                          • 10. Re: Why Grand Total For Lookup field is Blank
                            Sara Kim

                            Sorry for the late reply. Thank you so much for helping me, this works! You are a genius!