Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filter based on User-input variable

I'm trying to create something along the lines of a custom filter.   The user inputs a variable or selects it form the list, and it filters my charts.  Rather than having multiple filters for each chart on the same sheet, I would prefer to only have 1 filter, and create expressions within the chart to count based on the filter.  Currently, each chart's expression ignores selections for the other chart.

Current Expression:

count({$<[PeriodNumOrderPlacedonHold]=,[YearOrderPlacedonHold]=,[PeriodNumOrderCanceled]=,[YearOrderCanceled]=,[PeriodNumOrderFilled]=,[YearOrderFilled]=>} PeriodNumOrderOpened)

The expression above shows how many orders were canceled each period for each fiscal year in a line chart.  When applying filters to multiple fields YearOrderOpened, YearOrderFilled, YearOrderPlaceOnHold, and YearOrderCanceled, Qlik Sense automatically removes selections from some of the filters, and does not allow the charts to display the necessary data.

To get around this, I've created 2 user-input selection variables, and am trying to build that variable into the expressions. The user would be able to select the fiscal year in one field or 'All', and the second variable would allow to select anywhere from 1 through 12 and 'All'.

If(([Fiscal_Year]='All' and [Period_Selection]='All'),count(PeriodNumOrderOpened),

     If([Fiscal_Year]='All',count({$<YearOrderOpened={"=$(Fiscal_Year)"}>} PeriodNumOrderOpened),

          If([Period_Selection]='All',count({$<PeriodNumOrderOpened={"=$(Period_Selection)"}>} PeriodNumOrderOpened),

               count({$<YearOrderOpened={"=$(Fiscal_Year)"},PeriodNumOrderOpened={"=$(Period_Selection)"}>} PeriodNumOrderOpened))))

I've attached a screenshot of my sheet, and a mock data set below.


Order NumberPeriodNumOrderOpenedYearOrderOpenedPeriodNumOrderFilledYearOrderFilledPeriodNumOrderPlaceOnHoldYearOrderPlacedOnHoldPeriodNumOrderCanceledYearOrderCanceled
7561962017 62017
2034572016 92016122016
1412382016 12017
5123542015 8201522016