Filter last month when start of month but this month when not?

[Kuroha]

Hello everyone, i have a problem with quicksight’s default date filter and i’m not sure how to do it.

The request is like this: If today is first day of the month, date fillter will use start of last month as start date. Buf if today is not first day of the month, date fillter will use start of this month as start date instead.

I think it’s possible for each case (start of this month or last month), but i don’t know how to use both case depend on today’s date?

Is it possible to achieve something like this? Please let me know how to do it.

[mukilan]

Hi @Kuroha

You might be able to do this with a calculated field.

ifelse(now()=truncDate('MM', now()), date_column>=truncDate('MM', addDateTime(-1, 'MM', now())), date_column>=truncDate('MM', now()))

This calculated field checks if it’s the first day of month
yes - check and return 1 if the date is after or equal to last month beginning
no - check and return 1 if the date is after or equal to current month beginning

You can then add this field to the filters and filter in only when it’s equal to 1

[Kristin]

Welcome to the QuickSight Community @mukilan!
Thanks for reply to these questions and helping out the community!

[Kuroha]

Hi @mukilan, there seems to be a bit misunderstanding, i’ll give a bit more context.

The filter will also be shown on the sheet, like this:

The request is something like this:

• If today is first day of month (ex: Sep 1st), the filter will use start of last month till end of last month as filter value by default (ex: Aug 1st to Aug 31st).
• If today isn’t first day of month (ex: Sep 20), the filter will use start of this month till yesterday as filter value by default (ex: Sep 1st to Sep 19th).

Sure, it seems like this can be done using calculated field, but i don’t know how to show it on the filter? Because it looks like i can’t use calculated field as filter value and can only use parameter. But i don’t think this can be done using parameter?

The filter is actually showing on the sheet for user to see and use, those conditions are for default filter (if user change date filter it’ll also change accordingly of course, and reset filter will return it to default). I don’t know how to use those condition to actually reflect as value on the filter.

[debapc]

Hi @Kuroha - You may use dynamic default parameter by user group to achieve this. This is not an elegant solution. Let me explain the steps:

1. Create a user group. For example Default_User_Group and add all users to this group
2. Create a csv file (say, UserGroup.csv). Add UserGroup as header and Default_User_Group as value
3. Create a Dataset using the UserGroup.csv
4. Add a Calculated field (say, StartDate) to the Dataset. Here is formula for the Calculated field [I leveraged the formula from @mukilan’s response ]:

ifelse(now() = truncDate('MM', now()),truncDate('MM',addDateTime(-1, 'MM', now())),
truncDate('MM', now()))

5. Add the UserGroup DataSet to your Analysis
6. Add a Parameter (say, StartDateParam) in your Analysis
7. Setup dynamic default parameter value for StartDateParam using UserGroup DataSet. For Group name column use UserGroup and for Column for default value use StartDate
8. Create a Control of StartDateParam
9. Use parameter (StartDateParam) to setup your filter

Here is the documentaion on Creating parameter defaults in Amazon QuickSight.

Hope this solution works for you. I don’t feel it’s a perfect solution. I see the following feature request for QuickSight: “Ability to use calculated fields or variable values in parameters”. I am adding your influence to it. Thanks!

[Kuroha]

Hi @debapc, thanks for the reply. I think your solution may work, but that seems like quite a lot of workaround for something that should’ve been a Quicksight feature. I’ll mark it as solution for now.

I also agree, hopefully there’ll be a way to use calculated field or variable values in parameters. Parameters in Quicksight is a bit lacking imho.

[gillepa]

Hi @Kuroha -

you can actually take @debapc 's solution and make it a bit more elegant and simple:

In the data prep layer, create three new fields: a dummyGroup field with a value of “a” (one string value for all rows), and two date fields that would dynamically calculate the start and end dates based on whether today is the first of month or not

1. DummyGroup
   “a”

2. Start_Date

ifelse(
    now()=truncDate('MM',now()),truncDate('MM', addDateTime(-1, 'DD', now())),
    truncDate('MM', now())
    )

2. End_Date

ifelse(
    now()=truncDate('MM',now()), addDateTime(-1, 'DD', now()),
    addDateTime(-1, 'DD', now())
    )

Save and Publish then create two date Parameters in the analysis:

1. StartDateParam with the default date of 2001/01/01 (something in the distant past) and the dynamic default value using the DummyGroup as Group and Start_Date as value

2. EndDateParam with the default date YESTERDAY and the dynamic default value using the DummyGroup as Group and End_Date as value

Now add the Date you want as a Filter to the VISUAL (or visuals but not to the sheet) and make sure you use the Parameters for the Start and End date. This filter will be controlled by these two parameters, which means that you need to ADD the parameters to the Sheet as controls.

Publish the analysis. By default, users will see the start and the end dates you want them to see.

Hope it helps,
GL