Count within Date Range

Question & Answer
, , ,
1

Hello, got this formula;

distinct_countIf({Users}, {Hour} >= addDateTime(-90,‘DD’, ${EndDate}) AND {Hour} <= ${EndDate})…
This count the distinct user from the enddate parameter(Today) minus 90 days upto the enddate(Today)… Where the result is 32… But I need a condition that it will only count the users within the date range meaning Nov 17 is not counted. so the result will only be 31 counts.
image

2

Hello @danielreyes3rd !

What happens if you use your Hour field applied to a date filter that you layer on top of this target visual with your specified dates?

Let me know what happens/if it shows incorrect values.

3

Hello @danielreyes3rd !

Were you able to try my suggestion above or are you still running into this problem?

4

Hi, not sure what you meant here. You mean to the Hour field applied to the parameter date filter or date filter?

So technically there is a start and end date in the dashboard so we are not focus on the start date only the end date. For example the end date is Feb 15, 2024, based on the formula it is between end date minus 90 days and endate.

5

Hello @danielreyes3rd, just to clarify, is the issue that the first row of the table you posted above is being counted when it shouldn’t because the log date is the 17th? If that is the case, you will need to include the Log as well as the Hour date field when checking if it within 90 days of the end date. In the conditional logic above, you are only checking if the Hour date field is greater than or equal to 90 days before the end date.

I will post the suggestion I am making below:
distinct_countIf({Users}, {Hour} >= addDateTime(-90,‘DD’, ${EndDate}) AND Log >= addDateTime(-90,‘DD’, ${EndDate}) AND {Hour} <= ${EndDate})

I will mark my response as the solution, but let me know if you have any further questions. Thank you!

1 Like