Set Date Parameter based on Day of Month

Question & Answer
, , , , ,
1

I am creating two parameters called “ThisMonth” and “LastMonth” that I want to change after the 10th of the month. So essentially, my “ThisMonth” parameter would default to the first day of the current month only after the 10th. If the now()/current_date is anywhere between the 1st-10th day, my “ThisMonth” parameter would default to last month. My goal is to not have to manually update my date parameters each month.

Since I can’t create a Parameter off a calculated field, I need to find a way to basically get this query into two QuickSight parameters:

SELECT CASE
           WHEN date_part('Day', current_date) <= 10 THEN date_trunc('Month', dateadd(Month, -1, current_date))::DATE
           ELSE date_trunc('Month', current_date)::DATE END                     AS ThisMonthParameter
     , CASE
           WHEN date_part('Day', current_date) <= 10 THEN date_trunc('Month', dateadd(Month, -2, currentdate))::DATE
           ELSE date_trunc('Month', dateadd(Month, -1, current_date))::DATE END AS LastMonthParameter
2

Hi @stormsarah

Thanks for posting this question , since parameters cannot have expressions to calculate values dynamically. Could you please try the below solution and let me know if this helps.

  1. Create dataset with one row (use any of the existing datasource/table ) and create two calculated fields Current Month , LastMonth

Current-Month ==> ifelse( parseInt( formatDate(now(),‘DD’) ) <= 10 , addDateTime(-1,‘MM’,now()), now() )
Last-Month ==> ifelse( parseInt( formatDate(now(),‘DD’) ) <= 10 , addDateTime(-2,‘MM’,now()), addDateTime(-1,‘MM’,now()) )

image
image2548×524 52.6 KB

image
image1001×237 9.87 KB

2) Since this dataset will be used to refresh the parameter default values ,include the QuickSight security group which can access this dataset & rows.

  1. Create the parameters in analysis and update the dynamic default values based on the dataset created in the step1.

image
image1655×726 78.6 KB

  1. Repeat the step for each parameter , and validate/update the calculated field expression to return appropriate date values .

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”.

3

Thanks for the idea! I will try it out. Do you know if I leave the “group name” NULL in the data table, if that will default to applying the mapping to all groups? Or will I need to list every active group?

4