Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
New Contributor

Set A Variable On A Sheet With An Available Drop Down List

I am new to Qilk sense so any help or guidance would be greatly appreciated.

I have a field in a time series table that is called Data As Of Date.  On a lot of sheets, users can select one Data As Of Date to zoom in on a specific date or they select a range of dates to see trended views. I want to have a sheet with two separate pivot tables where the user can select a specific end date. When the user selects the end date, I want one pivot table to show a trended view of 24 months before the end date and I want the second pivot table to show a single metric based only on that end date.

Currently, my trended pivot table just shows data for all Data As of Dates that are selected. For the other table, I use a max function within set analysis to show the most recent metric. See below”

<[Data As Of Date]={'$(=Max([Data As Of Date]))'}>

This works but my only usability problem is that if a user wants to see a different end date then they go to a filter pane for Data As Of Date to select a different date.  But if the user still wanted to see a full 24-month window in the trended table, they would have to select all 24 dates. I think that is kind of messy.  My thought was to have a variable called vSetEndDate that can be set on that page. Which then both charts can utilize.  But I’d want the user to have a drop down of all possible dates that they can set that variable to. Is there a way to create a drop down on a sheet to set a variable that would  be the distinct list of Data As Of Dates.

Thank you,


1 Reply
Valued Contributor

Re: Set A Variable On A Sheet With An Available Drop Down List

Not that I know of, in a similar scenario, I used Sheet Navigation & Action extension on Branch to create buttons with set times, YTD, MTD, Quarter, etc. This can be used in a set expression to return the Months based on the selected "range". Note that its easiest with Set Expression to keep it numeric in this case, so I added a field to my Master Calendar called MonthsFromCurrent, 

Button for YTD sets MonthsFromCurrent filter using Select Value in Field to:

'>=' & Num(YearStart(MonthYear)))

& '<=' &  Floor(MonthEnd(Max(MonthYear)))