topic Count rows in range of dates in QlikView

Count rows in range of dates

Inna_Shnaiderman — Fri, 15 Nov 2024 23:16:47 GMT

Hi,

I have the following issue.

I need to count IDs in range of dates .

First date should be start of fiscal month. Fiscal month is 21st day of the previous month. Meaning if now is January, start of fiscal month is 21/12/2020. The second date should be yesterday.

So it should be something like (count distinct IDs where date>=21st of the previous month and date<=today()-1). Can you please kindly help me on it?

Thanks,

Inna

Re: Count rows in range of dates

AdiPai — Thu, 21 Jan 2021 11:29:45 GMT

Hi ,

Try This

COUNT(DISTINCT{<Date = {">$(=Date(MonthStart(Today(),-1)+20,'DD/MM/YYYY'))"}>}ID)

Thanks

Adi

Re: Count rows in range of dates

Inna_Shnaiderman — Thu, 21 Jan 2021 13:30:51 GMT

Hi, can you please assist where is the part it is till yesterday ?

Re: Count rows in range of dates

Inna_Shnaiderman — Thu, 21 Jan 2021 14:04:38 GMT

Hi, I found an answer , for the period till y-day it should be:

COUNT(DISTINCT{<Date= {">$(=Date(MonthStart(Today(),-1)+20,'MM/DD/YYYY'))"},

Date = {"<$(=Date((Today()-1),'MM/DD/YYYY'))"}>}ID)