9 Replies Latest reply: Jan 25, 2016 9:32 AM by Stefan Wühl

# # Months Over Target

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.

• ###### Re: # Months Over Target

Try removing the Only():

Count(DISTINCT if( (aggr( count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83),Month))

• ###### Re: # Months Over Target

May be this:

Sum(Aggr(If(Count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83, 1, 0), Month))

• ###### Re: # Months Over Target

may be like this?

=Count(DISTINCT if((aggr( count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83),Month))

• ###### Re: # Months Over Target

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?

• ###### Re: # Months Over Target

Try something like

Count(DISTINCT Aggr(If(Count({<RES = {'Y'}>} RecordID) / count(RecordID),Month) >= 0.83, Month), Month, ProdCat))

• ###### Re: # Months Over Target

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))

• ###### Re: # Months Over Target

What is the last line showing? That's not what I suggested.

• ###### Re: # Months Over Target

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.

• ###### Re: # Months Over Target

Well, hard to answer with the information provided. If possible, create and upload a small sample QVW.