topic Re: Count of ID's between two dates group by each date in App Development

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

varunreddy — Wed, 22 Dec 2021 16:15:09 GMT

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

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

varunreddy — Thu, 02 May 2019 03:39:27 GMT

Attaching the QVW for reference.

Thanks,

Varun

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

PrashantSangle — Thu, 02 May 2019 04:05:41 GMT

Where you want to implement that logic? in Front end or in script??

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

varunreddy — Thu, 02 May 2019 04:07:57 GMT

Front end.

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

varunreddy — Thu, 02 May 2019 04:09:26 GMT

Hi Prashanth,

Front end. I have also attached the sample QVW.

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

PrashantSangle — Thu, 02 May 2019 04:10:48 GMT

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

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

PrashantSangle — Thu, 02 May 2019 04:12:54 GMT

Note : Check Date Format.

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

Regards,

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

Vegar — Thu, 02 May 2019 04:13:02 GMT

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 )

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

PrashantSangle — Thu, 02 May 2019 04:21:54 GMT

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

Note:
careful with Date Format.

Regards,
Prashant Sangle