Hi, I had a need to split some fields into three alternate states so people could make a single set of selections among the fields, and related expressions would give counts based on different combinations of values present in those fields.
The three states are basically HA, Screening, Incentive.
I have one expression that looks at all three states to count users who have a value in HA, Screening, and only 1 item in OtherActivity like:
Count(Distinct
([HA]*[Screening]*[Incentive])
if(len(HADate)>0 AND len(ScreeningDate)>0 AND
Num(RangeSum(Aggr(TextCount(Distinct Incentive),UserId)))=1,
UserId))
Is there a way for me to adjust the above expression to identify the specific state/field combination of each item throughout instead of a lump at the top? or should I go back to script and reload the Incentive table with it's fields prefixed as HA.Incentive and Bio.Incentive for use in those states? (Hoping for an expression solution vs cloning tables.)
Normally the operator would select an ha date range in the HA state, a screening date range in the Screening state, and incentive activities in the Incentive state that were not the ha and screening to see 'who did the ha and screening and 1 other activity' in this case. (There are many more answers about other combinations being provided off the one set of selections made by the operator.) Sometimes the operator doesn't know the date range, or wants the whole season, and they want to just select the people who did ha or screening based on the fact they have '2015 HA' or '2015 Screening' in the Incentive field.
I've added an Incentive listbox to the HA state and Screening state so user can select those if they like, but now I'm stumped on how to adjust above expression to distinguish between the Incentive selections in the HA or Screening states, and those in the Incentive state. (And the Incentive name can vary slightly by some clients, so I can't just dictate the names of the incentives to look for.)
Please advise, thanks!
