Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a calculation that checks % records meeting target by month. This works well but as an additional col in a straight table I want to show the total # months the target was achieved.
Using examples online I have this :
Count(DISTINCT if( only(aggr( count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83),Month))
However its not working. Any ideas? 83% is target.
Try removing the Only():
Count(DISTINCT if( (aggr( count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83),Month))
Try removing the Only():
Count(DISTINCT if( (aggr( count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83),Month))
May be this:
Sum(Aggr(If(Count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83, 1, 0), Month))
may be like this?
=Count(DISTINCT if((aggr( count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83),Month))
Works great for the overall result, many thanks, however I also have product category, lets say "ProdCat" dimensions and the results are wrong for these in the table, I assume I need to add this dimension into the calculation as well?
Try something like
Count(DISTINCT Aggr(If(Count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83, Month), Month, ProdCat))
OK this works but the result is 1 greater than it should be. Now I could just add -1 and get the right answer but wonder why its one greater than it should be? Thanks for all replies by the way!!
(DISTINCT if((aggr( count({<SOT? = {'Y'}>} RecordID) / count(RecordID),Month,ProdCat) >= 0.83),Month,ProdCat))
What is the last line showing? That's not what I suggested.
Last line is showing 74 with your suggestion, plus the individual dimension results are incorrect. When I alter it to what I posted it shows 78, however the individual dimension results are correct, albeit over by 1? Again thanks.
Well, hard to answer with the information provided. If possible, create and upload a small sample QVW.