4 Replies Latest reply on Jun 27, 2016 8:06 AM by arjun.kumar.1

    Filtering with aggregate values across worksheets

    arjun.kumar.1

      Hey there,

       

      I'm using the Sales dashboard to illustrate the problem I'm having at the moment.

       

      My aim is to create alerts for those who have bought over X products (X Quantity) OR if they have been in the store > X visits (X Number of Records)

      The image below is unfiltered but it shows the Quantity & Number of Records.

      I used the below calculated field to do so, i.e. If a customer has bought over 40 products/quantity OR if they have been to the store above 10 times then I want Tableau to kick out a 'LOW ALERT' (Let me know if I've been unclear and I will try my best to expand)

      "

       

      IF (SUM([Quantity]) > 40 AND SUM([Quantity]) < 60) OR SUM([Number of Records]) > 10 THEN "LOW ALERT"

      ELSEIF (SUM([Quantity]) > 60 AND SUM([Quantity]) < 80) OR SUM([Number of Records]) > 15 THEN "MID ALERT"

      ELSEIF (SUM([Quantity]) > 80) OR SUM([Number of Records]) > 20 THEN "HIGH ALERT"

      ELSE "NO ALERT"

      END

       

      "

       

       

      When I apply this to my table, it is not working – why is Aaron Bergman showing up as Low Alert when he has over 20records, and his quantity is over 80. This should be classed as HIGH ALERT.

       

       

      I also want to apply the same filters to other worksheets i.e. to only include the customers who are deemed to be ‘HIGH ALERT’ but the filter ‘resets’ depending what sheet it is applied to.

      The filter seems to work when it’s not a measure, when I don’t include “SUM(X)” in the calculated field, the dimension can then be used on different sheets.

       

      Thanks in advance!

       

      Arj

        • 1. Re: Filtering with aggregate values across worksheets
          khalid norat

          Aaron is showing as low as your if statement is and Or and it satisfy the second part number of records > 10

           

          was this meant to be less than 10??

          1 of 1 people found this helpful
          • 2. Re: Filtering with aggregate values across worksheets
            Alastair Young

            Arjun,

             

             

            In response to your first query, the calculation is incorrectly ordered which I believe is the main issue why you are identifying Aaron Bergman as a Low Alert.

             

            Your nested IF statement is stopping at the LOW ALERT for Adam Bergman as his record meets the first If statement IF (SUM([Quantity]) > 40 AND SUM([Quantity]) < 60) OR SUM([Number of Records]) > 10 THEN "LOW ALERT"

            He has a SUM(Number of Records > 10) and therefore the criteria match is completed and a LOW Alert is returned.

             

            Try the following order, this should achieve what you are after.

             

            IF (SUM([Quantity]) > 80) OR SUM([Number of Records]) > 20 THEN "HIGH ALERT"

            ELSEIF (SUM([Quantity]) > 60 AND SUM([Quantity]) < 80) OR SUM([Number of Records]) > 15 THEN "MID ALERT"

            ELSEIF (SUM([Quantity]) > 40 AND SUM([Quantity]) < 60) OR SUM([Number of Records]) > 10 THEN "LOW ALERT"

            ELSE "NO ALERT"

            END

             

            Alastair

            1 of 1 people found this helpful
            • 3. Re: Filtering with aggregate values across worksheets
              arjun.kumar.1

              Thanks for your response, I'd ideally like the High Alert to have priority over the lower alerts - so as Aaron classifies as a High Alert, I want him to show as High and not Low.

               

              I assume I could fix this by putting high alerts to the top of the calculated field, I'll give that a go.

              • 4. Re: Filtering with aggregate values across worksheets
                arjun.kumar.1

                Thank you! That solves that problem, do you know how I could use this same filter on other sheets? Where the 'Customer' dimension may not be used e.g. Creating a blank worksheet with just the number of those who are deemed 'HIGH ALERT'

                 

                Arj