Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have a field "Status"
i want to sum all sales where Status = 'Approved'
i use this
sum( {<Status={'Approved'}>} Sales).
this works as axpected.
my problem is when i have only null() values in the Field Status.
in that case i would have like the expression above to return 0 (zero) or at least null
instead it just give me the sum of all sales regardless of whats in the Status Field (EG just all the sales)
how can i tell it to work as i need?
thanks
Daniel
And if you don't want to change the data model, try maybe
=Sum({<Status= {'Approved'}>*<Sales = {"=Count(Status)"}>} Sales)
May be try this:
Sum({<$*Status={'Approved'}>} Sales)
I think the most easiest solution would be to add a dimensional table for Status field, e.g. via an INLINE LOAD in your script, so that you have at least one value to make selections in:
STATUS:
LOAD Status [
Status
Approved
];
Even if your fact table only shows NULL in field Status, the set analysis should now work as expected.
And if you don't want to change the data model, try maybe
=Sum({<Status= {'Approved'}>*<Sales = {"=Count(Status)"}>} Sales)