Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi, im trying to add set analysis to an expression so that it sums 'IPTotal' for all 'Weeks' and where IPType = 'Daycase' and any values that dont match as null.
The expression that was used previously was:
Sum(if(IPType='Daycase',IPTotal,null()))
But the problem with this expression is that it didnt use set analysis to show all 'Weeks'
The expression i created to include set analysis is:
SUM({$<IPType = {'Daycase'},Week = {'*'}>} IPTotal)
But now the problem with my set analysis attempt is that this expression correctly shows all weeks but incorrectly doesnt show any data for IPTotal.
Please see image below:
Any Ideas?
hi miguel,
your solution doenst work as the values this returns are far too low (maybe showing nulls only)
with some trial an error i found the answer with the following expresion (a combination if set analysis + if statement)
SUM({$<IPType = {'Daycase'},Week = {'*'}>} if(IPType='',null(),IPTotal))
many thanks for your help though
Hello,
If you want to show all possible values for one field, usually you needn't to specify it in set analysis, as the expression will return them. So first try removing the Week part of it and see what shows.
Regards.
hi miguel,
Let me clarify again, i want the expression to show 'all weeks' by set analysis so that it ignores user week selection.
I think the problem with my set analysis expression is the null part.
Any ideas??
Then use
SUM({$<IPType = {'Daycase'}, Week = >} IPTotal)
to ignore user's selection for field Week. Otherwise, it will show all values for current selection, which is the current state when the chart is rendered.
In regards to null values, and checking your if() statement, you will not get any values if you don't have results.
hi miguel,
your solution doenst work as the values this returns are far too low (maybe showing nulls only)
with some trial an error i found the answer with the following expresion (a combination if set analysis + if statement)
SUM({$<IPType = {'Daycase'},Week = {'*'}>} if(IPType='',null(),IPTotal))
many thanks for your help though