Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I have a calculation like this:
(Both Numerator and Denominator work individually... but their division doesn't work)
Sum(Aggr(if(Sum([Is Engaged?])/(Count(distinct [Month Year])*4) >= 2,1,0) , [Party ID]))
/
Sum(Aggr(1,[Party ID])) // same as Count (distinct [Party ID])
What I have tried:
Put numerator and denominator separately in a variable and do the division on variables. Doesn't work
Use the div function.
Hoping I didn't do any silly mistake
Don't see anything wrong here... are you using these in a variable and then doing the division through a variable?
Hey Sunny,
I tried that as well. Do you think this could be a bug? Weird thing is that the numerator and denominator work well individually in KPI boxes.
Also I've noticed that....
Sum(Aggr(1, [Party ID])) is yielding 1 more than Count(distinct [Party ID])
for e.g...
Sum(Aggr(1, [Party ID])) =101
Count(distinct [Party ID]) =100
Shouldn't both be the same?
I would have thought that the two give the same number... not sure but may be there is a null Party ID, which is getting counted when using Sum(Aggr())...
Coming back to your division... would you be able to share a sample to check out?
"I would have thought that the two give the same number... not sure but may be there is a null Party ID, which is getting counted when using Sum(Aggr())..."
How can I exclude nulls in the Aggr? Sum({<[Party ID] - = {"*"}>} Aggr(1, [Party ID]))
Will provide sample data soon.
May be this
Sum({<[Party ID] = {"*"}>} Aggr(1, [Party ID]))
Thanks bro. Will check it out.
lol. Things got weirder... The following nonsensical tweak made it work.
Variables:
numer = (Sum(Aggr(if(Sum([Is Engaged?])/(Count(distinct [Month Year])*4) >= 2,1,0) , [Party ID])) *2 )
/
denom = ( Count (distinct [Party ID]) * 2)
Expression:
$(numer) / $(denom)
Suspect: [Is Engaged?] is of Dual type. (Used dual function here)
Difficult to say without looking at this...
Try with a bracket in the numerator
(
Sum(Aggr(if(Sum([Is Engaged?])/(Count(distinct [Month Year])*4) >= 2,1,0) , [Party ID]))
)
/
Sum(Aggr(1,[Party ID]))