topic Re: # Months Over Target in QlikView

# Months Over Target

dmac1971 — Mon, 25 Jan 2016 13:15:32 GMT

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

swuehl — Mon, 25 Jan 2016 13:18:33 GMT

Try removing the Only():

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

Re: # Months Over Target

sunny_talwar — Mon, 25 Jan 2016 13:19:28 GMT

May be this:

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

Re: # Months Over Target

Anonymous — Mon, 25 Jan 2016 13:19:57 GMT

may be like this?

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

Re: # Months Over Target

dmac1971 — Mon, 25 Jan 2016 13:25:33 GMT

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

swuehl — Mon, 25 Jan 2016 13:31:02 GMT

Try something like

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

Re: # Months Over Target

dmac1971 — Mon, 25 Jan 2016 14:11:52 GMT

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

swuehl — Mon, 25 Jan 2016 14:19:51 GMT

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

Re: # Months Over Target

dmac1971 — Mon, 25 Jan 2016 14:26:03 GMT

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

swuehl — Mon, 25 Jan 2016 14:32:29 GMT

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