# Months Over Target

[dmac1971]

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.

[swuehl · Accepted solution]

Try removing the Only():

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

[sunny_talwar]

May be this:

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

[Anonymous]

may be like this?

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

[dmac1971]

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?

[swuehl]

Try something like

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

[dmac1971]

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

[swuehl]

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

[dmac1971]

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.

[swuehl]

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