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

[varunreddy]

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

[varunreddy]

Attaching the QVW for reference.

Thanks,

Varun

[PrashantSangle]

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

[varunreddy]

Front end. 

[varunreddy]

Hi Prashanth,

Front end. I have also attached the sample QVW.

[PrashantSangle]

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

[PrashantSangle]

Note : Check Date Format.

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

Regards,

[Vegar · Accepted solution]

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 )

[PrashantSangle]

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

Note:
careful with Date Format.

Regards,
Prashant Sangle