Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I would like to use multiple columns as a filter in the filter pane
For example:
Table1:
Load
Jan_Pay,
Feb_Pay,
March_Pay,
April_Pay,
May_Pay,
June_Pay,
July_Pay
From Salary.xlsx
So I would like to show two filter in the chart. Filter names will be Q1Pay and Q2Pay
Q1Pay Q2Pay
Jan_Pay April_Pay
Feb_Pay May_Pay
March_Pay June_Pay
Thanks!
I've tried to interpret your issue in a different way in the solution below. I might be way of, if so then ignore my suggestion and instead try to explain in other words what you are trying to achieve. Maybe showing us the expected output from the salary input table in my example below.
It will not keep the one field per month structure, but will unpivot your excel and make your data model a bit more normalised.
//I use inline as source instead of your excel that I do not have access to.
Salary:
LOAD * inline [
Jan_Pay,Feb_Pay,March_Pay, April_Pay, May_Pay,June_Pay, July_Pay
100, 200, 300, 400, 500, 600, 700
110, 220, 330, 440, 550, 660, 770
];
CrossTable (Month_Pay, Amount)
LOAD
rowno() as Record,Jan_Pay,Feb_Pay,March_Pay, April_Pay, May_Pay,June_Pay, July_Pay
Resident Salary;
Drop table Salary;
//Q1Pay
LEFT JOIN LOAD * inline [
Month_Pay, Q1Pay
Jan_Pay,Jan_Pay
Feb_Pay,Feb_Pay
March_Pay,March_Pay];
//Q2Pay
LEFT JOIN LOAD * inline [
Month_Pay, Q2Pay
April_Pay,Jan_Pay
May_Pay,Feb_Pay
June_Pay,March_Pay];
I just want to use columns as rows so that i can use it in the chart as a filter
So columns
Jan_Pay, Feb_Pay and March_Pay should be under Q1_Pay
April_Pay, May_Pay, June_Pay should be under Q2_Pay
Result should be :
Q1_Pay Q2_Pay
Jan_Pay April_Pay
Feb_Pay May_Pay
March_Pay June_Pay
Can I use two cross tables to achieve this ?
Yes I would would portionsbitar used crosstable. Qlik Sense is made for filter field values not field name so by doing this normalising of you data model you will enable you to create a more generic solution applying filter on month/quarter values.