Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

varunreddy
Contributor III

Count of ID's between two dates group by each date

Hi All,

I am trying to count the ID's for multiple dates where Open Date is  in between Month Start and Month End for that particular date.

Date here is the dimension.

If the Date value is April 30 2019, I need all the ID's that have Open Date from April 1 to April 30.

If the Date value is March 30 2019, I need all the ID's that have Open Date from March 1 to March 30.

If the Date value is Feb 28 2019, I need all the ID's that have Open Date from Feb 1 to Feb 28.

Expression Used:

Count({<[Open Date] = {">$(=Date(Date#(Addmonths(max(Date),-1),'MM/DD/YYYY'),'MMM DD YYYY'))<=$(=Date(Date#(max(Date),'MM/DD/YYYY'),'MMM DD YYYY'))"}>}DISTINCT ID)

I have also attached the Excel file and the QVW for the reference. 

Thanks in advance!

Expected Output:

Date                    # ID

4/30/2019          2

3/30/2019          1

2/28/2019          1

 

 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Count of ID's between two dates group by each date

I have not been able to look at your files hence I'm in my mobile answering, but an alternative solution to your problem is to flag the Open Date rows that are in the month of the Date inside the script.

Load * ,
If(InMonth ([Open Date], Date, 0) ,1,0) as _OpenDateInDateMonth
from Excel

Then the expressing could be
=Count({<_OpenDateInDateMonth={1}>} distinct ID )
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
8 Replies
varunreddy
Contributor III

Re: Count of ID's between two dates group by each date

Attaching the QVW for reference.

 

Thanks,

Varun

Re: Count of ID's between two dates group by each date

Where you want to implement that logic? in Front end or in script??
Great dreamer's dreams never fulfilled, they are always transcended.
varunreddy
Contributor III

Re: Count of ID's between two dates group by each date

Front end. 

 

 

varunreddy
Contributor III

Re: Count of ID's between two dates group by each date

Hi Prashanth,

Front end. I have also attached the sample QVW.

 

 

Re: Count of ID's between two dates group by each date

If you want it in script then create flag and used that flag in front end
try below logic

Load Date,
OpenDate,
if(Date=MontEnd(OpenDate),'1','0') as cnt_flag
ID
From TableName;

Then In Front End
Take Straight Table
Dimension > Date
Expression > Count({<cnt_flag={'1'}>}ID) or Sum({<cnt_flag={'1'}>}ID)

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.

Re: Count of ID's between two dates group by each date

Note : Check Date Format.

if(Date=MonthEnd(OpenDate),'1','0') as cnt_flag

Regards,
Great dreamer's dreams never fulfilled, they are always transcended.
Partner
Partner

Re: Count of ID's between two dates group by each date

I have not been able to look at your files hence I'm in my mobile answering, but an alternative solution to your problem is to flag the Open Date rows that are in the month of the Date inside the script.

Load * ,
If(InMonth ([Open Date], Date, 0) ,1,0) as _OpenDateInDateMonth
from Excel

Then the expressing could be
=Count({<_OpenDateInDateMonth={1}>} distinct ID )
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted

Re: Count of ID's between two dates group by each date

in frontend try below expression
Count({<Date= {">$(=MonthEnd(max([Open Date])))"}>}DISTINCT ID)

Note:
careful with Date Format.

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.