10 Replies Latest reply on Oct 20, 2013 11:32 PM by Per Strid

    Conditional Table Calculation

    Per Strid

      How would I put a condition into a WINDOW_SUM?

      I'm trying to get a cross tab of occurrences. In the rows I have the first occurrence and in the columns the last time. This means that when row date is larger than column date it will be NULL.

      SNAG-0000.png

      What I want is WINDOW_SUM(SUM([Values]),0,LAST()) / TOTAL(SUM([Values]))

      BUT without getting values in the cells where nothing occurred.

      SNAG-0001.png

      It should be 100% in the diagonal and empty under...

       

      /Per

        • 1. Re: Conditional Table Calculation
          Jim Wahl

          There might be a simpler way, but I'd just use a conditional based on two INDEX() functions, one indexing row (Table Down) and the other the columns (Table Across)

           

          Because you have two different addressing paths for the INDEX() functions, you'll need to create a calculated field. Row Index = INDEX().

           

          And then:

          IF [Row Index] <= INDEX()  // this second INDEX() function will be the column index, if Compute Using > Table Across.

          THEN WINDOW_xxx(....

          END

           

          When you add this formula to the view, click the pill > Edit Table Calc. At the top you'll see a pull-down menu with selections for the current function and for Row Index. Make sure Compute Using is set to Table Across and Table Down, respectively.

           

          Jim

          • 2. Re: Conditional Table Calculation
            Jim Wahl

            Sorry, should have thought about it for five more minutes. I haven't tried this, but I think in this case, the following will work:

             

            IF NOT ISNULL(SUM(Values))

            THEN ...

            END

            • 3. Re: Conditional Table Calculation
              Jonathan Drummey

              Thanks for this, Jim, it gave me the light bulb of an idea I needed to solve a problem that's been on my mind for a long time!

               

              Jonathan

              • 4. Re: Conditional Table Calculation
                Jim Wahl

                It's going to cost you: Next time I'm at the Boston Tableau User Group, you'll have to sign your upcoming book, The Zen Masters' Guide to Tableau Zen

                • 5. Re: Conditional Table Calculation
                  Dan Huff

                  Have had to use this trick so many times

                  • 6. Re: Conditional Table Calculation
                    Matt Lutton

                    Would someone be able to post an example of this?  I've been really trying to wrap my ahead around table calcs, and I'd love to take a look.

                    • 7. Re: Re: Conditional Table Calculation
                      Jim Wahl

                      Hi Matthew:

                       

                      Check out the attached:

                       

                      Data

                      2013-10-18 18-16-46.png

                       

                      Indexes: Note how the table calc generates a value even if the cell is null.

                      2013-10-18 18-17-44.png

                       

                      Hiding bottom half: Row Index <= Col index

                      2013-10-18 18-18-15.png

                      • 8. Re: Re: Conditional Table Calculation
                        Matt Lutton

                        Excellent, thanks a lot Jim!

                        • 9. Re: Re: Conditional Table Calculation
                          Jonathan Drummey

                          That'll be a great Zen trick, signing a book that doesn't exist! (yet)

                           

                          Really great example! One thing that I find helpful in explaining how to see and identify domain completion is to show the marks count as well. For example, in the Data worksheet in your workbook the view out with just regular aggregates, there are 10 marks in the 4 rows by 4 columns. As soon as a table calc is added that addresses on one or more dimension(s) on Rows or Columns (but not all the dimensions), then domain completion is triggered and we see 16 marks on the rest of the worksheets. Paying attention to the cardinality of the dimensions in the view and the marks count can be a big help in diagnosing when we're getting unwanted domain completion and/or domain padding.

                           

                          One question that's up for me as I think about explaining this kind of stuff is what are the factors for when it's better to suggest doing the filtering for empty/Null values inside the calcs or to do them as something on the filter Shelf. For example, there's a tradeoff between putting more IF/IIFstatements inside calcs that make them more complicated vs. having simpler calcs and a filter. Yet the simpler calcs could end up using up a lot of CPU time prior to the filtering vs. the more complicated calcs. If you have any suggestions, I'm all ears!

                           

                          Jonathan

                          • 10. Re: Conditional Table Calculation
                            Per Strid

                            Thanks Jim!

                            Helped me out good. I will probably not show up in Boston for a while, based in Stockholm Sweden. But I used to be a lot there in my past. Love the city and the surroundings.

                             

                            Best regards

                            /P