Count if Sum between dates

[Anonymous]

Hello,

I am trying to count the number of locations that have met a monthly goal then divide that count by the total number of locations available. If the total applications submitted within the current month is greater than the monthly application target, count as 1.  Then I want to divide it by the number of total locations.

=count(if(sum({$<ApplicationDate={"$(='>=' & Date(MonthStart(Today())) & '<=' & Date(MonthEnd(Today())))"}>} NewAppCount)>=sum(Application_Targets),1))

/count(distinct LocationName)

=if(aggr(sum({$<ApplicationDate={"$(='>=' & Date(MonthStart(Today())) & '<=' & Date(MonthEnd(Today())))"}>} NewAppCount),MarketName)>=sum(Application_Targets),1)

/count(distinct MarketName)

Sample data:

Header 1	Header 2	Header 3
Location	Application Count	Target
A	50	40
B	35	40
C	38	35
D	40	30
E	35	35

Using the sample, there are three locations that are greater than or equal to the app target - and the desired output would be 60% of locations have met their current target this month.

Would I need to use the aggr() function to let QlikView evaluate each individual location's application count?

Any help is appreciated,

Phil

[sunny_talwar]

Try this:

=Count(Aggr(If(Sum({$<ApplicationDate={"$(='>=' & Date(MonthStart(Today())) & '<=' & Date(MonthEnd(Today())))"}>} NewAppCount) >= Sum(Application_Targets),1), LocationName))

/

Count(DISTINCT LocationName)

[sunny_talwar · Accepted solution]

Based on the criteria you use or >= (greater than and equal to) 4 location meet the criteria for your sample. Check it out

[Anonymous]

Thank you for the help and the corrected math.  The expression works perfectly!

Phil