0 Replies Latest reply: Jul 17, 2017 2:46 PM by Casey Marincin RSS

    how to aggregate a binary measure

    Casey Marincin

      I am working in Qlik Sense 3.2.  I am trying to create a dashboard for run successes on a list of products, by date.  A sample of my data is the following:

       

      ProductDateSuccess
      A2017-07-15Fail
      A2017-07-15Pass
      A2017-07-16Pass
      B2017-07-15Pass
      B2017-07-16Pass
      B2017-07-17Fail
      C2017-07-15Fail
      C2017-07-17Pass
      C2017-07-18Pass

       

      As you can see from this sample, not all products will run on the same day (e.g., C didn't run on the 16th), and each product can have 1+ runs on a single day (e.g., A ran twice on the 15th).

       

      My dashboard needs 2 visualizations -- a filter that lets the user select the product, and a chart that shows the overall success for each product over time (Date).  Via another posting, I learned that a bar chart can do this.  My current problem is that I do not know how to aggregate the Success "measure".  I have tried the following formulas for the measure in the bar chart:

       

      #1   If(Min(Success) = 'Fail', -1, 1)

      #2  Sum(If(Success = 'Pass', 1, -1))


      When I try formula #1, all I see are '1' values across the chart for every product, which is obviously wrong (relative to my sample data).  There are two problems with formula #2.  First, it gives unequal height bars.  Second, it could produce value '0' for a given day (if the number of Pass equals the number of Fail), which means that a user might not be able to drill into the given date.

       

      How can I correct my formula to produce my desired result?