Date Calculation error in Set Analysis

[rajarshi_guin]

Hi,

In my dashboard I have a measure which calculates the count of values between two dates which are selected in filters. The formula I am using for the measure is

Count(

{<[Transaction Date]={">=$(=getfieldselections([Start Date]))<=$(=getfieldselections([End Date]))"}>}

Distinct [Case Dimension Sequence ID])

In this measure I am taking the Count of Case Dimension Sequence ID for whom the Transaction Date is between the Start Date and End date values. The Start Date and End date values are selected from two filters.

So, if I select Start Date =09/01/2017 and End Date=09/30/2017 in the filter I should get the count of Case Dimension Sequence ID for whom the Transaction Date is between 09/01/2017 and 09/30/2017.

I have no problem with the Start Date condition. But in the End Date I am always getting the output till the previous of the day selected in the filter.

So, in the example above if I select Start Date =09/01/2017 and End Date=09/30/2017 in the filter I am getting the count of Case Dimension Sequence ID for whom the Transaction Date is between 09/01/2017 and 09/29/2017 whereas I should be getting the count of Case Dimension Sequence ID for whom the Transaction Date is between 09/01/2017 and 09/30/2017.

This problem is occurring for any End date I select.

Can anyone please let me know what needs to be done in this case.

Thanks,

Rajarshi

[OmarBenSalem]

can you change it as follow:

Count(

{<[Transaction Date]={">=$(=getfieldselections([Start Date]))<=$(=MAX([End Date]))"}>}

Distinct [Case Dimension Sequence ID])

and see what happens?

[sunny_talwar]

Seems like Transaction Date is a TimeStamp... either change this to just date in script like this

Date(Floor([Transaction Date])) as [Transaction Date]

or try this

Count({<[Transaction Date]={"=Floor([Transaction Date]) >=[Start Date] and Floor([Transaction Date]) <=[End Date]"}>}

DISTINCT [Case Dimension Sequence ID])

[rajarshi_guin]

Hi Sunny,

This option has worked. I had converted the fields previously to date format but did not use the floor function. After using the floor function it is working. Thanks.

[Anonymous]

If you "converted the fields previously to date format but did not use the floor function", you only changed the presentation,but it was still a timestamp.
Now it's a good time to mark Sunny's answer as correct.