8 Replies Latest reply on Aug 28, 2018 12:44 PM by Joe Oppelt

    sum calculated value occurrences across a dimension

    Todd Shannon

      I am trying to track and sum when a sum'd value goes from > 0 to zero from one month to the next.  I have an expression that seems to capture the change pretty well and it is the following:

       

      zeros.PNG

      zero values

       

      IF sum([Counts]) == 0 and LOOKUP(sum([Counts]), -1) > 0 then 1 else 0 END

       

      ones.PNG

      If I aggregate the changes across a "product" dimension, the values are displayed properly with a '1' in the field for the product and month where the value went from  > 0 to 0.  But I'm not interested in the individual occurrences as much as I'm looking the the total across all "products".

       

      When I try to remove the "product" dimension it doesn't sum all the 1's--I just get zeroes across the canvas.  I cant figure out why.  I even tried a "grand total" row, but still nothing.

       

      The attached workbook has the calc's for anyone who can help.  Thanks!

        • 1. Re: sum calculated value occurrences across a dimension
          Joe Oppelt

          So do you want a sum of all the 1s for product "a", and for product "b", etc?

           

          That's what I did here.  See attached.

          • 2. Re: sum calculated value occurrences across a dimension
            Joe Oppelt

            Or do you want to know that two products had at least one occurrence?

             

            Or do you want to see all products with the total of occurrences next to them (without the dates?)

             

            We can mess with these table calcs 100 different ways depending on what you need to see here.

            • 3. Re: sum calculated value occurrences across a dimension
              Todd Shannon

              Thanks for the follow up, Joe. 

               

              What I need is to actually see the sum value of all the 1's for all products.  My example only have a few occurrences, but Feb and April should have 1 occurrence for each month and the other months should be zero.  When I remove the product dimension, all the months go to zero, which isn't correct.  It should be as I stated above, 1 for Feb and April based on the example data. 

              • 4. Re: sum calculated value occurrences across a dimension
                Joe Oppelt

                In the attached on Sheet 1, I took the Window_sum calc off the sheet.  The [Zero values] calc gives you the 1s and 0s you need.  Because it uses LOOKUP, it's a table calc.  This screen shot shows the table calc setting that makes it work with the setup on the sheet:

                 

                Table across says within each row (and the sheet specifies the rows by [Product], run the calc along the values in the row.  It does what we want it to do here because of the way the sheet is laid out.

                 

                Next I made a copy of the sheet, and in it I changed the table calc settings to specify the order and direction of dimensions.  It results in exactly the same thing, but now I am forcing Tableau to evaluate the calc on my terms.

                 

                 

                Notice that I selected "Specific Dimensions".  I also dragged Product to the top of the list in that box (by default it was the opposite order).  So I want Tableau to evaluate along Dates, for every Product.  Note also what I circled in the screen shot.  I want the calc to restart every product.  (I don't want the last date value in the first product to be compared against the first one in the second product.)

                 

                Go to copy 3 of the sheet.  Here I dragged [Product] to the details shelf.  We have to have [product] on the sheet, because this calc needs to be evaluated within each product.  Notice that there are 5 marks per date.  That's because we have 5 products.  We will address this later.


                Go to Sheet 1(4).

                 

                Here I made a new calc:  [total per month] .  It's the equivalent WINDOW_SUM as the one we saw yesterday (except instead of embedding the zero-value code in the WINDOW_SUM parentheses, I dragged [zero value] into the parentheses.)  For this one, it's important to compartmentalize [zero value] as a separate calc.  I'll get to that.

                 

                When I first drag [total per month] onto TEXT, I get the sum across the row like we saw yesterday.  But I want taleau to evaluate this DOWN the table, not across.  I have it set this way on the sheet, and you see a value of 1 in each mark down the two months that have something.  Edit the table calc and see the setting I have.  Here's a screen shot:

                 

                Notice that I selected "Specific Dimensions" again.  Only this time I have Dates first in the list, and I restart every Date.  So now I'm looking at all those 1s and 0s in [zero values] and summing them down the chart for each date.

                 

                Notice too that I have circled a pulldown in my screen shot.  We now have a nested table calc here.  Go into this edit box and do that pull down and select [zero values].  You will see that for the nested calc I have the setting to evaluate that calc across each product as we saw in sheet 2.  So I have the nested calc evaluating one direction, and the total calc evaluating in a different direction.  (This is why I needed to have the inner calc compartmentalized as its own calc.)  There is a lot of power in our ability to nest and direct calcs like this!

                 

                go to Sheet 5.  Here I moved [Zero values] from text to details.

                 

                On Sheet 6 I added another calc.  [index].  This lets me look at the index values as Tableau sees them.  Bu default it evaluates table(across), but I want to get a value of 1-through-N along the list of products.  So here is how I set it:

                 

                 

                Just run along products.  Now I essentially get a count of products.

                 

                Go to Sheet 7.  I dragged [index] to the filters shelf.  (By dragging it, it retains the table calc settings I made for it.  If you dragged it from the measures list to filters, you would have to reset the calc settings.)  Then I edited the filter to select only for value =1.  Now we get one row displayed, with the values you need to see by [Dates].

                 

                See attached.

                • 5. Re: sum calculated value occurrences across a dimension
                  Todd Shannon

                  Joe,

                   

                  This is a fantastic answer and solution! I don't know if there's another way to do it, but this way helps me understand much of what's under the hood in tableau and how to leverage window and table calculations more effectively.  Bravo, good sir! 

                  • 6. Re: sum calculated value occurrences across a dimension
                    Joe Oppelt

                    when you try to implement this in your actual application, you might run into hurdles.  Don't hesitate to reply back here if you do.  I'll get an email that you updated the thread.

                    • 7. Re: sum calculated value occurrences across a dimension
                      Todd Shannon

                      I've got a hurdle already :-)

                       

                      How would I use these numbers in other calculations?  Sort of like we did with the [zero values] calculation, I need to relate these numbers to other fields and make the results apart of other calculations. 

                       

                      Is there a way to make the window/table calcs apart of the calculated fields so that we don't need to edit the orientation of the table calc later, or does the fact that the calc is so dependent on the view and LOD that this becomes impractical? 

                      • 8. Re: sum calculated value occurrences across a dimension
                        Joe Oppelt

                        Because I've set the specific dimensions for the table calcs, the sheet layout no longer matters.  The dimensions themselves still have to be on the sheet (like I did with [Product]), and that might cause multiple values per cell on the sheet (which I weeded out using [index]).

                         

                        Can you give an example of what types of calculations you need to use these in?  I can help you access them in those calcs, and maybe that would be enough to help you move forward with it in your actual application.