Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Filtering on filter selected

Dear reader,

The title might be a little confusing. I'll explain what I wish to do and I hope that clarifies my question.

I wish to create 2 identical charts next to eachother, apart from 1 detail.

I've got the data below, sadly this is spread out over different tables (a table for employee, a table for incident, and a table connecting them.)

%Employee_keyEmployee.Name
1111AAAA
2222BBBB
3333CCCC

%Incident_keyIncident.CategoryIncident.TSOC
11A10
22A5
33A25
44B15

%Employee_key%Incident_key
111122
111144
222211
333333

Now I wish to show 2 bar charts next to eachother, each showing the TSOC per Category. This is easily doable, BUT... Depending on my selection I want the first chart to show it for one employee, and the second for another.

So if I choose Employee 1111 and Employee 2222, the first chart shows this info for Employee 1111 and the second for Employee 2222.

But if I choose Employee 2222 and Employee 3333, the first chart shows this info for Employee 2222 and the second for Employee 3333.

Now ofcourse I could use a Fixed Number limitation and choose Top 1 and Bottom 1, but I'd like to find a way to achieve this for 3, 4, or even 5 charts next to eachother.

I tried to achieve this using the aggr() function, but if my understanding of it is correct this create a new table of whatever is calculated by the aggr() function and thus it loses all connection to other dimensions.

firstsortedvalue(aggr(SUM({$<Type={'Incident'}>}[Incident.TSOC]),[%Employee_key),[%Employee_key])
firstsortedvalue(aggr(SUM({$<Type={'Incident'}>}[Incident.TSOC]),[%Employee_key]),[%Employee_key],2)

These are the formula I tried to use (the Incident type is because the table also contains Problems and Changes).

Sadly, since the aggr() loses the connection to Incident.Category it is unable to connect itself to the Category dimension I've set up.

Ofcourse the result of this formula is only an Employee key (due to the firstsortedvalue), and not the values per category...

Something else I've tried is to add the firstsortedvalue to the measure like this:

SUM({$<Type={'Incident'},[Employee.Name]={Aggr(firstsortedvalue([Employee.Name],[%Employee_key]),[Employee.Name])}>}[Incident.TSOC])

Sadly this results in this error: "Error in set modifier ad hoc element list: ',' or ')' expected."

It took me a while to settle for firstsortedvalue, simply because this worked in a table. I was trying to find a way to choose the first selection in my filter, and then the second selection, and this was the only way I found that worked:

firstsortedvalue([Employee.Name],[%Employee_key])

firstsortedvalue([Employee.Name],[%Employee_key],2)

Does anyone have any better ideas how to solve this issue?

Yours Sincerely,

Casper Westelaken

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Hi Casper,

I believe your scenario would be an ideal candidate for the "Alternate State" feature.

see link here ... https://community.qlikview.com/docs/DOC-20467

Essentially you are able to separate chart interactions from each other, linked via a "statename".

Best of luck.

Regards,

LA.

View solution in original post

3 Replies
Highlighted

I am not entirely sure I understand the requirement, if you select 1111 and 2222 what are the numbers you expect to see on your first chart and the second chart?

Highlighted
Partner
Partner

Hi Casper,

I believe your scenario would be an ideal candidate for the "Alternate State" feature.

see link here ... https://community.qlikview.com/docs/DOC-20467

Essentially you are able to separate chart interactions from each other, linked via a "statename".

Best of luck.

Regards,

LA.

View solution in original post

Highlighted
Contributor II
Contributor II

Hi LA,

This is exactly what I was looking for! Thank you very much!

Yours sincerely,

Casper Westelaken