Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Showing results for 
Search instead for 
Did you mean: 
peter_bolin
Contributor II
Contributor II

How to count values between two dates (variables)

I've seen a few questions regarding this, which have been very helpful, but I still can find the answer to this.

I created two variables, one which calculate three days before the first day of a month and the second which calculates three days before the end of a month. Here they are:

vEndDateminus3=  date(monthend(APH_DATE)-3, 'M/D/YY')

vStartDateminus3= date(MonthStart(APH_DATE)-3, 'M/D/YY')

I have values called COI which I'd like to count how many occured between vStartDateminus3 and vEndDateminus3. This is what I have:

Count({<{'$(=vStartDateminus3)'} = {'>=$(=vEndDateminus3)'}>}[COI])

Not working! Any help would be appreciated!

5 Replies
Qrishna
Master
Master

try like this:

Count({$<APH_DATE = {'>=$(=vStartDateminus3) <=$(=vEndDateminus3)'}>}[COI])



Thiago_Justen_
MVP
MVP

I'd suggest you:

Count({$<APH_DATE = {">=$(=$(vStartDateminus3))<=$(=$(vEndDateminus3))"}>}[COI])


Double quotes are needed here.

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Digvijay_Singh
MVP
MVP

Or may be -

Count({$<APH_DATE = {">=$(vStartDateminus3)<=$(vEndDateminus3)"}>}[COI])

jonathandienst
Partner - Champion III
Partner - Champion III

If the variables are defined as dates, you will need

Count({<APH_DATE = {">='$(vStartDateminus3)' <='$(vEndDateminus3)'"}>} COI)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
peter_bolin
Contributor II
Contributor II
Author

Thanks everyone, very helpful! I ran this to check if I could 1) use this logic as measure called #aphs 2) make sure there are COI's within the start date-3...end date-3 range and 3) audit my minus3 variable. 

#aphs = Count({<APH_DATE = {">='$(vStartDateminus3)' <='$(vEndDateminus3)'"}>} COI)

I'm very new to Qlik so sorry for the delay response and perhaps my filters are the issue. Thanks agian.