There is an observed behavior where the ‘<=’ (less than or equal to) operator in calculated fields used for date comparisons does not consistently perform as expected, the results are equivalent to using < operator.
I have a date column named ts and 2 date parameters $startDate2, $endDate
I created a custom calculation named “T_Total” that returns the “T_Amount” column value if ts is between $startDate2 and $endDate, otherwise returns 0.
The $startDate2=01 Aug 2023 and $endDate=31 Aug 2023. The actual output below shows for 31 Aug, the T_Total is 0, instead it should be same value as T_Amount as per <= operator but it seems to be working like < operator
As an alternate solution, I am adding one day to endDate and then using < operator.
This discrepancy in operator behavior can lead to inaccuracies in date-based filtering, potentially affecting data analysis and visualization within QuickSight reports.
@Zerry00 - Thank you for posting your query. Unfortunately, when I tried to replicate this scenario at my end, I could not as it is giving me proper values as expected. Can you please share the parameter configuration details? I have shared mine below for your reference.
I am using rolling defaults in date parameters, here are the parameter details:
Thanks @Zerry00 for the details. Unfortunately, I could not replicate the issue at my end as its giving me the data perfectly as expected. Sharing you the snapshots. Sorry, that I could not help you. Perhaps you can try to open a Support case with AWS from your end.
Thanks for looking into it. Just a concern, is the end date parameter set to rolling default? because I feel that’s the only possible difference!
Yes @Zerry00, I set it up using rolling defaults as well. I didn’t keep the same settings as yours as my sample dataset only had older data. Please see the settings that I have for the endDate parameter.
Got it. I re-checked the same, in case I was missing something, but it is still happening. Here is a video for your reference.
Data Source: Postgres SQL
Data Storage: Spice (set on Full Refresh)