Hi all
I have inherited a Quicksight dashboard and analysis (table) which includes a calculated field and a filter which uses the calculated field. It also has controls where a user sets dates for the desired data window. This is the calculated field
This is the calculated field formula:
ifelse(
{Line Item Status} <> “Successfully Complete” AND {Line Item Due Date} >= ${pBillableInProgressStart} AND {Line Item Due Date} <= ${pBillableInProgressEnd}, “Yes”,
{Completed Date} >= ${pBillableWindowStart} AND {Completed Date} <= ${pBillableWindowEnd}, “Yes”,
“No” )
The controls have been set to:
BillableInProgressStart - 01/10/2023
BillableInProgressEnd - 31/10/2023
BillableWindowStart - 01/10/2023
BillableWindowEnd - 31/10/2023
There is filter applied to the table which looks for the calculated field value of “Yes”.
Data with a completed date of 31/10/2023 is being excluded from the table. According to the formula any line item with a completed date of less than or equal to BillableWindowEnd shoujld be marked yes, and therefore included in the “yes” filter. However, the data is only included in the table if the Billable Window End is set to 01/11/2023.
Any ideas on what might be going wrong? I’m a bit of a novice so simple guidance would be much appreciated!
Hello @WeeksH, welcome to the QuickSight community! Thank you for the detailed explanation of the issue you are facing, it is incredibly helpful in the debugging process.
My thought is that the parameter value for pBillableInProgressEnd is including HH:mm:ss that are set to 00:00:00. This would mean that your values would only return up to midnight of the final day and include none of the values returned throughout the day. If you go to edit that parameter, and check the dropdown for Time granularity, make sure that is set to Day. That should resolve the issue you are facing.
Hi @DylanM thank you for the quick response. I checked the controls and the date pickers are set to YYYY/MM/DD excluding a timestamp. But I think you are right and in selecting a date it is only returning data up to midnight.
I think I need to play around with the controls to see if I can get them to include the full 24 hours of the day, or otherwise advise users they need to choose the first date outside of their desired window. (i.e. 1st December for results within November). One final thing I may try is to see if I can update the calculated field formula to include timestamps.
Hi @DylanM
I realise now you mentioned the parameters and I was looking only in the control settings. By amending the parameters themselves so the time granularity was set to minutes, I am now able to retrieve data up to 23:59 on the end date which is set.
Problem solved - thanks!