Apply Filter to Dimension for a Table Chart

[KennyTan]

Hi all,

Good day. I am pretty new to Qlik Sense, and i have some question on implementation and would appreciate any input/insight provided.

I have a use case to implement some kind of "default view" on a table chart. Meaning to show certain range of dataset when a sheet is loaded without any user selection filter, something like "GetCurrentSelections())=0".

For e.g., I have a wide range of Customer data within different year. I would like to apply a filter to a table chart to only show customer data for year = 2020 only when the sheet is first open/loaded. Is this scenario possible?

I had tried to applied some suggestion as below on Customer dimension column, but doesn't seem help in my case, the aggr seem not working and the chart still showing all the customer data instead of only year 2020.

aggr(ONLY({<YEAR={'2020'}>}customer),customer)

Below expression show an invalid dimension when i tried.

IF(COUNT(GetCurrentSelections())=0
, Aggr(ONLY({<[Year]={'2020'}>}[customer]), [customer])
, [customer])

Hope i explain my issue clearly. Appreciate any input.

Thanks,

KY.

[Kushal_Chawda]

@KennyTan  try below

aggr(ONLY({<YEAR={'2020'}>}customer),YEAR,customer)

[KennyTan]

Hi @Kushal_Chawda ,

Thanks for your input.

Apologies if I cannot make my requirement clear, still new to Qlik.

The suggested method will only limit to dimension data belong to year 2020, but if user choose another year filter, this may not work anymore. Is it possible we put in some IF condition to the dimension? I tried using :

IF(COUNT(GetCurrentSelections())=0, , )

But not sure whether it is not allow or I implement in the wrong way, it show invalid dimension.

Basically what my requirement was:

- to show only 2020 data when the sheet is loaded by default

- the data set will filter based on filter panel criteria, for eg, if filter data for 2019, then only 2019 data will show on the table chart.

Is that possible? I also attached a sample qvf if that may help.

Thanks. Looking forward to heard from you soon.

Regards,

KY.

Added:

I understand this requirement can be achieve by applying the limitation on measure level, but is it possible to do it on dimension level? The attached qvf is just a simple sample, i may need to implement this requirement on other visulization as well, etc, chart, map, etc.

[Kushal_Chawda]

@KennyTan  it will be better to limit using measure like below instead of using calculated dimension. You can use same set expression in all measure.  I have assumed the measure as "sum(Sales)" you can change accordingly

=if(getselectedcount(Year)=0, sum({<Year={"2020"}>}Sales), Sum(Sales))

[KennyTan]

Hi @Kushal_Chawda ,

Thanks, but adding set analysis on the measure seem just limit the calculation, not helping on my use case. Apologies if not make my requirement clear, i attached another similar copy of the app for easy reference.

For eg, my sample dataset is below:

[year, customer, sales
2018,A,12
2019,A,43
2019,B,12
2019,C,34
2019,D,76
2020,E,14
2020,B,4
2020,C,65
];

I would like the sheet when loaded default show only 2020 data set, which is something similar as below:

Only when user select different [year] filter, for eg, year 2019, the dataset will change accordingly.

Ultimately, when the sheet 1st loaded/opened, the dataset show should be similar like 1st image. 

That the reason why I tried to apply set analysis on Dimension level instead of measure level, to limit the dimension data set.

I'm still new to Qlik, not even know whether this is possible? If not please let me know. Appreciate your input.

Hope can heard from you soon. Thanks.

KY.

[Kushal_Chawda · Accepted solution]

@KennyTan  restricting from dimension will be more complicated as compared to restricting from measure. In the end, using measure you will be able to serve what you need. See the attached

[KennyTan]

Hi @Kushal_Chawda ,

Thanks, that is what I need. Didn't realize Aggr can be used in such a way. Thanks. 

Just one point i'm not understand, can please let me know why there is a need to * Avg(1) in the formula? What is the purpose of this please?

sum(aggr(rangesum( above( sum({$<customer>}sales),0,rowno())), 
(customer,(=sum({$<customer>}sales), DESCENDING))))*avg(1)

Besides, may i ask is there any issue for using 

IF(COUNT(GetCurrentSelections())=0, , )

instead of 

IF(GetSelectedCount(year) = 0, , )

Wouldn't the 1st one is more suitable for my use case? I assume 2nd one only apply of there is [year] being selected as filter?

Thanks,

 KY.