6 Replies Latest reply on Oct 7, 2013 4:15 AM by Dawid Nawrot

    Removing row if there's a gap in value

    Dawid Nawrot

      I've got a table like that:

       

      test.png

       

      I would like to remove currencies that do not have a value in all columns. I've tried filtering in different ways but with no luck.

       

      I've got one Column (Month), One Row (Currency) and one Value Mark (Sum Of Visits)..

       

      how do I go about applying such filter to get rid of those currencies that stopped producing visits?

        • 1. Re: Removing row if there's a gap in value
          Matt Lutton

          You cannot filter on an aggregated measure (like: Sum(visits)>0), so I'm not sure if this is possible.  Someone may have a workaround, which I would be interested to see as well.

          • 2. Re: Removing row if there's a gap in value
            Dawid Nawrot

            Wouldn't that be an equivalent of something like HAVING SUM(Visits) > 0 and then counting number of values returned, if less than 12 do not display.. I would be very much interested in finding a way to do this

            • 3. Re: Re: Removing row if there's a gap in value
              Jim Wahl

              Hi Dawid,

               

              I think you're on the right track with your logic. Here is an example from Superstores where you might want to filter Alabama, Arkansas, and other states that do not have sales in every month of 2013.

              2013-10-07 11-04-56.png

               

              Starting with the final field (you'll have to enter this after the below, but it helps to see the goal): States with Missing Values =

              [Number of State Values] < [Number of Columns]
              

               

              Add a calculated field Number of Columns =

              SIZE()
              

               

              When entering this table calc, click on the Default Table Calculation blue text in the upper right corner of the dialog box and select Order Date. After entering a table calc field, I will usually drag it to the Tooltip button to verify it's working. Here you can see that SIZE() == 12 for all rows, even Arkansas which is missing two months.

              2013-10-07 11-11-06.png

               

              We'll use another table calc, WINDOW_SUM(), to find Number of State Values =

              WINDOW_SUM(IIF(SUM(Sales) > 0, 1, 0))
              

               

              IIF is just a shorthand way of writing IF SUM(Sales) > 0 THEN 1 ELSE 0. This should also be computed over the Order Date dimension. Again, I added it to the tool tip, and you can see it returns 10 for Arkansas.

              2013-10-07 11-17-13.png

               

              Now you can add the Boolean States with Missing Values shown above and add that to the Filter shelf and set it to False. And you'll get the below (you can remove Number of Columns and Number of State Values from the view).

              2013-10-07 11-19-53.png

               

              Jim

              1 of 1 people found this helpful
              • 4. Re: Re: Removing row if there's a gap in value
                Jonathan Drummey

                Matt Lutton: "You cannot filter on an aggregate measure (like: Sum(Visits)>0)…" is not accurate. We can always do a  filter on a continuous aggregate measure like SUM(Visits) and set that to at least 1, or for example here's a filter on SUM(Sales):

                 

                Screen Shot 2013-10-07 at 4.41.46 AM.PNG.png

                 

                While you are correct that we cannot filter on a discrete regular aggregate measure such as a boolean "SUM(Visits)>0", there are two workarounds. One is to turn that into a continuous value, with a  formula like IF SUM(Visits) >0 THEN 1 END, the other is to turn the evaluation into a table calculation like LOOKUP(SUM(Visits),0), because we can add filter  table calculation results.

                 

                Jim Wahl: On another thread Dan Huff pointed out that the new 8.1 RANK functions ignore the missing values. This leads to another technique for identifying the number of states w/values: WINDOW_MAX(RANK_UNIQUE(SUM([Sales]))), which I would typically write as PREVIOUS_VALUE(WINDOW_MAX(RANK_UNIQUE(SUM([Sales])))) so the calc is evaluated only once in the partition. I'm mentioning it because I think it's more elegant and easier to describe than the embedded filter calc that we've had to use prior to 8.1, however, I'm not sure this will be faster than a PREVIOUS_VALUE(WINDOW_SUM(IIF(SUM(Sales)>0,1,0))), I'll wait until the production 8.1 to test that out.

                • 5. Re: Removing row if there's a gap in value
                  Jim Wahl

                  Dawid,

                   

                  Jonathan's addition of PREVIOUS_VALUE() around the WINDOW_SUM(IIF(...)) for Number of State Values is a nice optimization that I often forget.

                   

                  With a table calc function like WINDOW_SUM(), Tableau is calculating this value for each cell. This is not obvious in the example, but if you drag Number of State Values to the Text shelf, you'll see the value 12 in each cell, and Tableau is calculating the SUM of the IIF() results 12 times per row. You can optimize this with PREVIOUS_VALUE(), which returns the previous value in the partition, unless it's the first row in the partition, in which case it executes the function inside the (). Therefore, it's only doing the math once per partition (State in the above example).

                   

                  With the amount of data in the example, the difference in performance is probably < 100 ms, but it's a nice tweak regardless and helps re-enforce how table calcs work.

                   

                  Jim

                  • 6. Re: Re: Removing row if there's a gap in value
                    Dawid Nawrot

                    I managed to do it, it's great. I will have to use similar solutions all the time...

                     

                    I'm gonna study those functions now to get my head around it so I don't forget!