Top N filter based on value and latest calendar date - 1

[balawatt]

Hi team! Need some help here. I wanted to select Top N locations based on sum(value) desc and also latest calendar_date - 1 selected by the user. For example

If I select 8/7-8/17 & if today is 8/17 it would sort based off 8/16
If I select 8/7-8/20 & if today is 8/17 it would sort based off 8/16
If i select 8/1-8/5, & if today is 8/17 it should sort based off 8/5 (not 8/4)

Currently, its sorting based on sum(value) desc and whatever calendar_dates are displayed. For example, from the pic, its sorting based on sum(value) desc from 8/8 through 8/12 .

[duncan]

Hello @balawatt , welcome to the Quicksight Community!

For the date part, I would recommend using parameter controls for date filtering and using a calculated field to determine how to find the date. For example:

Start Date parameter = ${StartDate}
End Date parameter = ${EndDate}

ifelse(
now() >= ${StartDate} AND now() <= ${EndDate}, 
addDateTime(-1, 'DD', now()), 
now() >= ${EndDate} and {calendar_date} = ${EndDate}, 
{calendar_date},
NULL
)

[balawatt]

Thanks duncan, its identifying