Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

skyline01
New Contributor

how to aggregate a binary measure

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?