I have an Excel file containing rule id, number of failures and run date. There can be multiple runs of the same rule in a month. In the example file attached, there are 10 rules, run on 1st and 15th of every month from Jan to Apr.
Two views are required: Intermediate view and Final view
1) Intermediate view - Create a column called SUB_STATUS that will have one of the 3 options - New, Existing, Passed
a) If number of failures is 0, then Passed
b) If number of failures for that rule id in the previous month aggregated for all runs in that month is 0, then New
c) Otherwise, Existing which means that the rule had failures last month as well
I asked this view question in one thread and which was very wonderfully answered by Shawn - https://community.tableau.com/thread/202543
But I am facing problem in getting the Final view
2) The Final View requires the count of New, Existing and Passed for every month. The catch is every rule's SUB_STATUS is to be consolidated into one irrespective of the number of runs in that month. The conditions for consolidation are
a) If the SUB_STATUS of all runs is the same, the final status for that RULE_ID is retained the same
b) If the SUB_STATUS of any one run in that month is either NEW or EXISTING, then the final status for that RULE_ID is NEW or EXISTING
c) the final status of the RULE_IDs need to be summed up for each New, Existing and Passed status for every month
Seems to be a challenging one. Wondering if this is possible at all in Tableau?
Excel file attached. If you can provide any pointers, screenshots or provide with a solution, it would be a huge help
If you share any workbooks, please use Tableau v9.0 as that is the one I have with me