4 Replies Latest reply on Sep 28, 2018 6:59 AM by Jake Anderson

    Flag a group of data as "bad" when a single line item meets certain criteria

    Jake Anderson

      Hi All,

       

      I'm working with rows of data and have been trying to flag an entire Month's worth of data as bad when at least one line item meets certain criteria.  In this case, each line item has an age, and if that age falls into a certain age bucket, it's flagged as a bad thing.

       

      LineID     Age     Age Bucket     Good/OK/Bad     Date

      1               5          1-15 Days               Good          9/13/18

      2               17          16-30 Days            OK             8/10/18

      3               35          31-90 Days            Bad            7/21/18

       

      So above is a sample of what my data looks like (imagine many more rows).  I have added the Good/OK/Bad column as a calculated column based on the age bucket (which itself is a calculated column based on Age).

      I've had different versions of this problem across multiple different data sets, and it doesn't always work consistently.  Probably because I don't fully understand how to aggregate the Age Bucket correctly.

       

      Ultimately, what I'm trying to do is show for population of line items (say, a month's worth), whether or not there was at least one item where the Good/OK/Bad=Bad.

       

      I've created a field like this:

       

      If ATTR(Age Bucket)= "31-90 Days" and SUM(Number of Records)>0 Then "Bad"

      ELSEIF ATTR(Age Bucket)="16-30 Days" and SUM(Number of Records)>0 Then "OK"

      ELSE "Good"

      END

       

      This does a fine job of separating out each line item into Good/OK/Bad categories and let's me apply colors to them.  However, I want to be able to remove the Age Bucket dimension from the view and just show:

       

      Month     Good/OK/BAD

      September     Good

      August            OK

      July                 Bad

       

      Sometimes it works properly, sometimes it doesn't and I can't pin down why.

       

      Any ideas?

        • 1. Re: Flag a group of data as "bad" when a single line item meets certain criteria
          Joe Oppelt

          You want to use LOD here.

           

          You probably have a series od dimensions you've broken this across.  I'm assuming you don't want your entire business flagged as bad for the month if one customer or geographic region should be marked bad.  So let's say Dim1, Dim2 and Dim3 are the dimensions that define the chunk of data that should be marked BAD for the month.

           

          { FIXED [Dim1],[Dim2],[Dim3], DATETRUNC('month', [Date field]) : MAX( if [Good/OK/Bad] = "Bad" then 1 else 0 END) }

           

          First:  where did I get that datetrunc mess?  I simply dragged the MONTH([Date field]) that I have on my rows/Columns shelf into the calc editor.  (This assumes you are breaking the data by months using a date field.  If you have a month field already, then just use that here.)

           

          This will go through all rows with in each defined dimension set for each month.  If any rows have BAD, then the 1 gets computed for that row, and the MAX will find the biggest value of all the 1s and 0s among that data set.  So if even one row is set to 1, then the final calc value for this LOD calc will be 1 for all the rows.

           

          Caveat:  I need to know how you calculated [Good/Bad/OK].  If it is an aggregate calc, this won't work, and we'll need to find a more complicated way to do this.

          • 2. Re: Flag a group of data as "bad" when a single line item meets certain criteria
            Jake Anderson

            Let's assume it's purely based on a single dimension (age bucket)  and that you don't need to bring Month into the calculation.  Any time I'd want to see it broken down by month, I'd keep it included in the visualization itself.

             

            Also, I do want to flag the entire group of data as bad if I see the one instance as bad (or OK, or Good).

             

             

            Good/Bad/OK is calculated like this.

            If ATTR(Age Bucket)= "31-90 Days" and SUM(Number of Records)>0 Then "Bad"

            ELSEIF ATTR(Age Bucket)="16-30 Days" and SUM(Number of Records)>0 Then "OK"

            ELSE "Good"

            END

            • 3. Re: Flag a group of data as "bad" when a single line item meets certain criteria
              Joe Oppelt

              Upload a sample workbook if you can't get this to work.  I'll show you in that.

               

              First of all, your [Good/Bad/Ugly] calc is already an aggregate, so we're going to have to use table calcs instead of LOD here.


              The principle is still the same though.  You'll do a WINDOW_MAX instead of LOD MAX.  It will look something like this:

               

              WINDOW_MAX( if [Good/Bad/OK] = "Bad" then 1 else 0 END )

               

              The trick will be in getting the table calc settings right, and that will depend on what you have on the sheet.  And that's why I would need a sample workbook.


              If proprietary data is an issue, check out the video linked here:

               

              Video demonstrates how to anonymize your workbook/data

               

               

              I wouldn't need all your data.  Just two small sets of rows -- one set that shows no BAD, and one set that has at least one BAD.

              • 4. Re: Flag a group of data as "bad" when a single line item meets certain criteria
                Jake Anderson

                Thanks Joe - I used a version of your first response to get the results I need (basically the same except I didn't need the LOD expression).

                 

                Basically a few prelim calcs followed by an overall one:

                 

                calc1 - If bad then 1 else 0

                calc 2 - if ok then 1 else 0

                overall calc- If SUM(Calc1)>0 Then "Bad" ELSEIF SUM(Calc2)>0 Then "OK" ELSE "Good"

                 

                Thanks!

                 

                Jake