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 | | | | |
