Filter based on User-input variable

Wed, 25 Nov 2020 16:16:04 GMT

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 Number	PeriodNumOrderOpened	YearOrderOpened	PeriodNumOrderFilled	YearOrderFilled	PeriodNumOrderPlaceOnHold	YearOrderPlacedOnHold	PeriodNumOrderCanceled	YearOrderCanceled
56789	1	2017	2	2017
98765	2	2017	3	2017
123456	3	2017	4	2017
321654	4	2017	5	2017
543286	5	2017	6	2017
75619	6	2017					6	2017
20345	7	2016			9	2016	12	2016
14123	8	2016			1	2017
13537	8	2017	10	2017
51235	4	2015			8	2015	2	2016
412346	12	2016	2	2017

topic Filter based on User-input variable in QlikView

Filter based on User-input variable