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:
| Product | Date | Success |
|---|
| A | 2017-07-15 | Fail |
| A | 2017-07-15 | Pass |
| A | 2017-07-16 | Pass |
| B | 2017-07-15 | Pass |
| B | 2017-07-16 | Pass |
| B | 2017-07-17 | Fail |
| C | 2017-07-15 | Fail |
| C | 2017-07-17 | Pass |
| C | 2017-07-18 | Pass |
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?
