topic Count if Sum between dates in QlikView

Count if Sum between dates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Count if Sum between dates

sunny_talwar — Thu, 21 Apr 2016 20:46:54 GMT

Try this:

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

Count(DISTINCT LocationName)

Re: Count if Sum between dates

sunny_talwar — Thu, 21 Apr 2016 20:50:39 GMT

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

Re: Count if Sum between dates

Anonymous — Thu, 21 Apr 2016 21:13:22 GMT

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

Phil