1 2 Previous Next 22 Replies Latest reply on Jan 31, 2018 1:15 PM by Khoa Nguyen

    How to hide blank columns created by table calculations

    ankit godha

      Hi,

       

      I am doing year over year calculations on date. See the attached image so for example I want to see the % difference between sales of 2012 Vs 2013 and I created a table calculation for it.

       

      But it's showing the blank column for 2012 which I don't want to show on the grid. How can I achieve that ?

       

      I am attaching my workbook for you to use for yourself

       

      hide_2012_in_%difference.png

        • 1. Re: How to hide blank columns created by table calculations
          Noah Salvaterra

          3 Calculations:

           

          2012 Sales: if datepart('year',[Order Date])=2012 then [Sales] else 0 end

           

          2013 Sales: if datepart('year',[Order Date])=2013 then [Sales] else 0 end

           

          % Difference: sum([2013 Sales])/sum([2012 Sales])-1

           

          Sorted?

           

          N.

          • 2. Re: How to hide blank columns created by table calculations
            Bill Lyons

            Right-click on the Sum(Sales) % difference table calc pill, select Filter, then select Special, then select Non-null values, and click OK. The column will vanish.

            1 of 1 people found this helpful
            • 3. Re: How to hide blank columns created by table calculations
              ankit godha

              Hello Bill,

               

              When I use your solution, the 2012 sales column also vanishes. That is not what I need. I just need to hide the blank column in the table calculation

               

              thanks

              -Ankit

              • 4. Re: How to hide blank columns created by table calculations
                ankit godha

                Hi all,

                 

                Adding a new workbook to show the expected view, which I was able to put together using a dashboard but it doesn't seem like the right way to me.

                 

                See the attached workbook with the "expected view" dashboard. How can that be achieved in a grid ?

                 

                thanks

                -Ankit

                 

                @Noah Salvaterra

                 

                your solution doesn't work either as it creates zeroes in the view for 2012 data and 2013 data , see sheet2 in the attached workbook. Please let me know what I am doing wrong

                 

                thanks

                -Ankit

                • 5. Re: How to hide blank columns created by table calculations
                  Noah Salvaterra

                  Like this? I'm not exactly answering the question, but rather suggesting another way to achieve the desired view.

                  N.

                  • 6. Re: How to hide blank columns created by table calculations
                    Noah Salvaterra

                    OK... I went a little crazy with this one and added a (multiple) table calculation version which utilizes 2 different types of data densification (and at least a couple other tricks), it is on the tab labeled "Advanced". I'm not kidding when I say it is likely to be extremely confusing to anyone who hasn't spent months musing over these topics. For this reason I can't recommend it as a way to solve this problem in production (unless all else fails...). Basically I'm just posting it to further for Jonathan Drummey and Joe Mako (maybe a couple other folks) who enjoy musing over these issues and might like to unravel the puzzle of why I did it the way that I did.

                     

                    N.

                    • 8. Re: How to hide blank columns created by table calculations
                      Jonathan Drummey

                      @Noah - very nice! I hadn't seen using a .5 bin to double the years, that's fantastic!

                       

                      @Ankit - the key difference between Noah's 3 calculations view and your view is the presence of YEAR(Order Date) in your view. Noah's view is built using record-level calculations that only return the desired values, whereas your view has YEAR(Order Date) creating the partitions that the aggregate calculations are occurring in. Noah's solution effectively moves that partitioning "down" into the record level calculation.

                       

                      Jonathan

                      • 9. Re: How to hide blank columns created by table calculations
                        Noah Salvaterra

                        Not quite double, 2n-1 which does the trick exactly in terms of the number of rows needed, but the trouble just starts there since I wanted them in a different order and with different labels and with a complete domain even when years are filtered.

                         

                        2 challenge questions that came out of this:

                         

                        1. Can this (or another) technique be modified (built) so that the % difference computes the percent difference across the table for non filtered years? I.e say the year 2011 is filtered from the view, then the 2012 % difference shows 0% (actually it is undefined, but I wrapped the calc in a zn to avoid blanks in the table). Could it instead show the difference between 2010 and 2012? I've got a foggy idea of shifting things around so they are actually hanging on a different year, but it leads to a host of other problems to solve.

                         

                        2. I created the labels dynamically with a calculation based on the parity of an index calculation, but I had to sort these labels manually (not the underlying bins though, since that would wreck the table calc). Could the sort be done dynamically as well?

                        I managed to crack this one before I posted it... updated workbook attached, it resulted in an unintended side benefit of an optional column divider (updated workbook below).

                         

                        OK, I promised 2 challenge questions... how about this...

                        2. Could bins of size .34 be used to show both differences and % differences? I think so and the dynamic sort calculation I just added could then be used as a filter on the values, differences or % differences. Since it is downstream of a table calc, order of operations would push it down far enough in the pipe that is shouldn't mess up anything else... I think...

                         

                        Anyway, I'm going to think about these too, but my wife just told me it is time for bed, so I figured I'd throw the gauntlet and hope for a day off tomorrow.

                         

                        N.

                        • 10. Re: How to hide blank columns created by table calculations
                          Noah Salvaterra

                          #2 done... the answer is yes. Why was I thinking that would be hard? The better question actually is why the year (bin) now seems to be required on the columns shelf, instead of just in the level of detail? I must be missing something.

                           

                          Oh well. Bed time.

                           

                          N.

                          1 of 1 people found this helpful
                          • 11. Re: How to hide blank columns created by table calculations
                            Noah Salvaterra

                            Insomnia...

                             

                            #1 Instead of zn, an ifnull could be used to select the next year as needed. This would need to be built out for however many years you'd want to be able to skip over (max of 2 here makes it a bit simpler). Unless there is a clever way to carry this value over via a previous_value() calculation, it seems a bit tricky with all the other moving pieces though. I was able to carry the sales value from the non-filtered year via a previous_value() table calc.

                             

                            If anybody else was working on these I sincerely apologize.

                             

                            N.

                            1 of 1 people found this helpful
                            • 12. Re: How to hide blank columns created by table calculations
                              Bruce Segal

                              Jonathan:

                               

                              Thanks a ton for the high level context - the main diff is the presence of YEAR(Order Date) in your view. Noah's view ... uses record-level calculations that only return the desired values.... - it's helping me understand Noah's solution. I still have to dig deeper to understand it, but this context helps a ton.

                              • 13. Re: How to hide blank columns created by table calculations
                                Bruce Segal

                                Noah:

                                 

                                Very nice solution. I have some questions to better understand it.

                                 

                                1. (Referring to "ankit_table_calculation_NS2") Can you give some context on what setting the bin size to .5 for [Year (bin)] does. I get that it puts 2 years into one bin. And I get that you only use it on the level of detail shelf. So I think doing that "aggregates" 2 years together. And I think that some how is how you end up comparing the most recent year to the past year. However, if I understand that correctly, and I'm not sure I do, then I still can't envision how it all comes together.

                                 

                                2. Referring to "ankit_table_calculation_NS5." In the custom calc that's a measure, named "Index," you use the following formula

                                "if [index]%3=1 then 0

                                elseif [index]%3=2 then 1

                                else 2

                                end"

                                 

                                I'm not familiar with using %n after a function (e.g. [Index] ) And, I don't see it documented in Tableau in any of the help text for functions. Is this a way to set the order in which the [Index} function applies to the data, e.g. down, across, etc? Can you let us know what it is, how it works, and if it's available to be used with all functions?

                                 

                                3. A fundamental question - and heh it wouldn't be Sunday if I didn't ask you another one of these fundamental questions - Shouldn't it be easier to do this in Tableau?

                                 

                                I encounter Ankit's exact question very, very frequently. It seems like it shouldn't take a Tableau Zen Master several hours on a weekend (ignoring his wife's call "Honey, it's time to sleep.") to develop a workaround to a common need of Tableau users. I'd say two Tableau Zen Masters to recognize the value of Jon's contribution, and don't want to overstate the amount of effort it took to develop this work-around.

                                 

                                (Setting aside, for the moment, the data viz perspective that the blank column for 2012 adds context and shows unmistakeably there is no YoY data for 2012)

                                 

                                Bruce

                                1 of 1 people found this helpful
                                • 14. Re: How to hide blank columns created by table calculations
                                  Yuriy Fal

                                  Hi all,

                                   

                                  Noah, thank you for literally "decimating" Year (bin) -- it is inspiring.

                                   

                                  One more approach could be used -- a plain old blend :-)

                                  Please find your wb with Sheet 7 attached.

                                   

                                  Yours,

                                  Yuri

                                  1 2 Previous Next