Counting Records between two dates

[Markbhai]

Hi All.

I am trying to count the number of records in my database which fall between 2 dates 'Target Date' and 'Issued Date'  So that I can tell how many items were issued on time.

I am using the following as an expression but getting zero as a result, however there are at least 4 which are issued within the target.

Count({<[Target Date] ={"<$(Date([Issued Date]"}>} [id])

Example dataThanks

[Lisa_P]

Try adding in the only function:

Count({<[Target Date] ={"<$(Only([Issued Date])"}>} [id])

This will work if you are in the table with the id dimension, but to count overall, you would also need to aggregate by id and sum eg Aum(Aggr(Count({<[Target Date] ={"<$(Only([Issued Date])"}>} [id]), id)

[MayilVahanan · Accepted solution]

Hi

If both Dates are in the dimension, you can use "if" condition

Count(If([Target Date] < Date([Issued Date]), [id]))

Or
Sum(Aggr(If([Target Date] < Date([Issued Date]), 1), Id))

[Markbhai]

Thanks MayilVahanan - Both of those work, although I prefer the first option as it seems more obvious to me (now I see it).  I have only been working with Qlik for a day or so and haven't really studies the Aggr function yet.  I am not clear why we need the trailing ',1)'.

Maybe this is my study for today.

Thank you.

[MayilVahanan]

Hi

Aggr function creates a virtual table based on dimension. In our case, we are doing aggr function with ID, and requirement is calculating number of Ids. 
Virtual table is like

ID Value

ID1, 1

ID2, 1
..

ID10, 1

After that, we are dng sum, so it will give 10.
If you use like below also, will give same result
Count(Aggr(If([Target Date] < Date([Issued Date]), Id), Id))