There are 2 Parameters in my analysis, From_Date and To_Date. There is a between filter applied on abc_Date column where the condition is like abc_Date between From_Date and To_Date.
For whatever date I enter in From_Date, only first of the month needs to be considered. For e.g. If I enter 3/15/2021 in From_Date, then it needs to be considered as 3/1/2021.
Similarly for whatever To_Date is entered, last of the month needs to be considered.
Is there any option of achieving this in QuickSight? Thanks in advance
@Vignesh_Kannan - This can e achieved by using truncDate function in QuickSight. You can utilize truncDate only to bring it to the first date of the month for the FromDate Parameter. For end date of the month from ToDate Parameter you need to use the combination of truncDate and addDateTime functions. Sharing the links below for your reference for those 2 functions.
Snippet of the relevant portion from example :
Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as âSolutionâ. This will help the community to find guidance and answers to similar question. Thank you!
1 Like
As soon as user enters the From_Date, the date value needs to be changed to 1st of the month. Truncdate function can be used in Calc field, but how the same can be used in the parameter and filters?
@Vignesh_Kannan - You donât need to change the value in Parameter. You need to create a calculated field as I mentioned using the functions and then use those fields in your filters to filter the data according to your use case. Hope this helps!
@Vignesh_Kannan - Just to provide you more insight, see the below example implementation.
Create a calculated field like âMatching Criteriaâ using your From Date and to Date Parameter and then use that in filter like Matching Criteria = 1 to show only the qualified records. Hope this clarifies!
ifelse(Date >= truncDate(âMMâ, ${paramFromDate}) AND Date <= addDatetime(-1, âDDâ, addDateTime(1, âMMâ, truncDate(âMMâ, ${paramToDate}))), 1, 0)
Please note that you need to convert that new calculated field to âDimensionâ
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as âSolutionâ. This will help the community to find guidance and answers to similar question. Thank you!
1 Like
@sagmukhe I have a similar question - I would like to have some KPIs with filters using these parameters- I would like to have date from : deriving from the dateto: and make it first day of the year - this is achievable frmo calculated fields but you cannot filter visual from calculated fields which makes it really diffcult to deal with when you want to use simple distict # during the period that can be easily achievable from KPI etc., but probably at this point I have to create more calculated fields to achieve this- Iâm assuming.