Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i have a complex set of conditions depending on values of the Fact table, each of which might result in a different figure (e.g. 3, 4 and 5 for each of the following).
=if(source1='1',(count( {$< STATUS = {'A', 'B'}>} DISTINCT ITEM_ID)),
//Next line for counting the items with Status = C and any Resolution except F or G.
if(source='1',(count( {$< STATUS = {'C'},RESOLUTION= {"*"} -{'F', 'G'}>} DISTINCT ITEM_ID)),
if(source='2',(count( {$< STATUS = {'A', 'D'}>} DISTINCT NS_BUG_ID)))))
I'm looking for the aggregate of those three (or two, or one) resulting figures, or zero if none of the conditions are met.
Having sum(aggr( in front of everything above doesn't seem to work. I guess i'm using something wrongly...
Any ideas are welcome.
Thanks!
I'm looking for the aggregate of those three
What is your dimension to aggregate ?
Thanks for your quick hand!
I'm just counting distinct items.
I can make an example app.if that helps...
Btw, i noticed a small typo in my example above (source1 instead of source), but that's not what's breaking my code
Found another typo (NS_bug istead of ITEM) that i fixed too, but doesn't solve it. Don't know how to attach the sample_A.qvw file i prepared...
aah... got to use the advanced editor to attach files 🙂
Ok, try to enclose your expression with something like this:
=Sum(aggr(...put your complex expression here..., status))
But I see the problem with your if expression:
=if(status='1', ...this works when status=1...,
if(status='1', ...this never works...,
if(status='2', ...this works when status=2)))
Try to understand how the if works and how you use it.
Thanks! You're right; using IF statements overcomplicates a simple sum that can also be done like this (now it works!)
=(count( {$< STATUS = {'A', 'B'}, source= {'1'}>} DISTINCT ITEM_ID))+
//Next line for counting the items with Status = C and any Resolution except F or G.
(count({$< STATUS = {'C'}, source= {'1'},RESOLUTION= {"*"} -{'F', 'G'}>} DISTINCT ITEM_ID))+
(count({$< STATUS = {'A', 'D'}, source= {'2'}>} DISTINCT ITEM_ID))