Hi Qlik Sense Community,
I have an calculation (set analysis) created for a few different visualizations but I want to give the user the ability to drill down to a specific program. There are four programs: CFO, General Relief, CF, and CW.
Currently, I have created four different measures that all use the same base calculation but there is one added filter in the set analysis that separates each measure from one another. Unfortunately this approach will not work because the user will not be able to drill to a program using a measure.
I am trying to create a field in the load script that will bucket the records into each program but if the record should exist in multiple buckets, then group it that way. Below is the current If (case statement) that I have tried applying but records that should be in multiple buckets are only placed in one.
if(PGM_COMBO like '*GA*','General Relief',
if(PGM_COMBO like '*CW*','CalWORKs',
if(CFO_IND = 1,'CFO',
if(PGM_CODE='FS','CalFresh')))) as Program
For example, Below is a table showing each program at the top and the calculations for each month (disposition Month). If this was converted to a bar chart, I want the user to be able to select not only a specific month, but also a specific program that will be used to filter on.
| Disposition Month | CF | CW | CFO | GR |
| Nov-19 | 99.71% | 99.70% | 99.51% | 99.98% |
| Dec-19 | 99.33% | 99.39% | 98.55% | 99.89% |
| Jan-20 | 99.53% | 99.39% | 99.18% | 99.89% |
| Feb-20 | 99.58% | 99.14% | 99.23% | 99.94% |
| Mar-20 | 99.56% | 99.53% | 99.40% | 99.71% |
Does anyone have any suggestions?
Edit:
Here is an example of when the records should be counted (if record is 1 then it will be counted):
| records | pgm_combo | CFO | FS | GR | CW | CFO |
| 1 | CW,GA,FS | Y | 1 | 1 | 1 | 1 |
| 2 | FS | Y | 1 | | | 1 |
| 3 | FS,GA | Y | 1 | 1 | | 1 |
| 4 | FS,CW | Y | 1 | | 1 | 1 |
