Dynamic Date Filter

Rohan Hazarika shared this question 3 months ago
Answered

Hi

Im having issues when using the dynamic date filter on my 'Set Analysis' function.

When i select the predefined period as yesterday, the data comes up fine. However, if i use current day-1 there is no data displayed.

I'm guessing this is because current day is taking the timestamp as well. How do i get around this? I need to filter on current day-3 to current day-2.

Best Answer
photo

Hi Rohan,


Thanks for sending that screenshot though, that has helped a lot.


I think your issue is actually the operator you are using. Setting the Date value EQUAL to a value will give you a single value. I think you should be using the operator BETWEEN instead. Then you will be able to set a range of dates and achieve the timeframes that you are after.


/2d30c33c64542913ea5b96cbed9c7512


Let me know if this is what you are after.


Kind regards,

Simon

Comments (8)

photo
1

Hi Rohan,


Thanks for your question.


Are you able to provide a little more information about your Report, including:

  • The Data Type of the Filter that you are using?
  • Whether this behaviour restricted to Set Analyses functions?
  • The resulting SQL for the Report you are trying to create?

I will move this ticket to private if it contains information that you wish to keep confidential.


Generally speaking, there are two types of time formats in Yellowfin. There is relative time, that is relative to the User viewing the Report (i.e. Users in different timezones) and 'absolute' time. For example, the difference between the pre-defined filter 'Yesterday' versus 'Last 24 Hours'.


I suspect in your case, 'Current Day - 1' is behaving like 'Last 24 Hours', where Yesterday would be displaying data from 0:00 to 11:59 of the previous day depending on your Timezone, and the Timezone of the data. My understanding is that there would be no way to get the equivalent to 'The Day Before Yesterday' and 'The Day Before That', as they are not prebuilt functions, however, I can check for you as you may be able to achieve something through Custom Functions. If your Users span across Timezones, I would suggest asking if that is actually what you want as Users may be seeing different data depending on where they are.


Let me know if I've understood this correctly.


Kind regards,

Simon

photo
1

Hi Simon

Timezone is not an issue here.

And im not sure what the difference is between 'Yesterday' and 'Current Date - 1 Day'. I would assume them to return the same value. Or are you saying that the way 'Yesterday' and 'Current Date - 1 Day' fetches the data is different ways?

photo
1

Hi Rohan,


Would you be able to send me the SQL of the queries when you are using both types of filters so I can understand how these values are being calculated?


/e1ec87240690ab085c9fe6574562280a


In my example, the dynamic date filtering seems to be working correctly, so it would be useful to know how your query looks.


Kind regards,

Simon

photo
1

Hey Simon,

I dont have the filter on my report. The report contains all my data stretching back to 2years.

I am building 4 charts based on the data in the report with the differentiation between the 4 charts being :

1. Data for yesterday

2. Data for day before yesterday

3. Data for last 7 days

4. Data for current month


I am the 4 different charts on the same report (since the underlying data is the same). To differentiate between the days, i am using the 'Set Analysis' function.


When configured like below, because of choosing yesterday, it works fine.

/6c311de411e64ea09c3aeab5c912be90


But now, for the day before yesterday chart, there is no pre-defined period. As such i am setting it to 'Current Day - 1' but it doesnt work.

/afa57473facf99b802c231a131f5afbb


I am guessing that 'current day -1' is looking at my current time (21/06/2021 4:17:00) and subtracting 24hrs and trying to get a value for (20/06/2021 4:17:00)

whereas

'Yesterday' is truncating my current time (21/06/2021 4:17:00) to just a date (20/06/2021 00:00) and then subtracting 1 day from it. and getting (20/06/2021 00:00:00) .


If thats the case, how can i create a chart to show values for 'day before yesterday'

photo
1

Hi Rohan,


Thanks for sending that screenshot though, that has helped a lot.


I think your issue is actually the operator you are using. Setting the Date value EQUAL to a value will give you a single value. I think you should be using the operator BETWEEN instead. Then you will be able to set a range of dates and achieve the timeframes that you are after.


/2d30c33c64542913ea5b96cbed9c7512


Let me know if this is what you are after.


Kind regards,

Simon

photo
1

Hi Rohan,


I just wanted to check in to see how you are traveling with this issue. Please let me know if the solution below did not resolve your issue and I can look into this further. Otherwise, I will shortly go ahead and mark this as closed.


Kind regards,

Simon

photo
1

yes, that worked. Thanks again

photo
1

Thanks for the confirmation Rohan!