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!
You could create a filter for the Quarter and the Month.
A Master Calendar is helpful for creating the quarters, months, years, etc
If you select a quarter from the Quarter filter, then the months from that quarter will be the only available in the Month filter
@dwforest Thanks for your reply but this is a sample data i used for example and the real data is not related to the quarter or months. Is there any other possible ways to achieve this requirement. Any functions we can use?
Not without the real data example.
Try something like this.
Table1:
Load Jan_Pay, Feb_Pay, March_Pay, April_Pay, May_Pay, June_Pay, July_Pay,
Jan_Pay & '|' & Feb_Pay & '|' & March_Pay as %q1pay,
April_Pay & '|' & May_Pay & '|' & June_Pay as %q2pay
FROM Salary.xlsx
FOR EACH vPay in 'q1pay', 'q2pay'
LOAD [%$(vPay)],
Subfield( [%$(vPay)],'|') As [$(vPay)]
Resident Table1;
NEXT vPay
@Vegar Thanks for your reply 🙂 That script is not working 😞 . I will try something different with resident.
I'm sorry, but I was typing on my mobile device. Did you understand the principle of my suggested solution.
You need to create a field for each of the quarter collections you want. You could do this as I suggested (just adjust the syntax to fit your scenario). Or by concatenating the three values from Table1 three times using the same output field name.
@Vegar I tried applying your script but I got different results. please look at the attached screenshot.
Probably something like below
Table1:
Load
Jan_Pay,
Feb_Pay,
March_Pay,
April_Pay,
May_Pay,
June_Pay,
July_Pay,
rangesum(Jan_Pay,Feb_Pay,Mar_Pay) as Q1_Pay,
rangesum(Apr_Pay,May_Pay,Jun_Pay) as Q2_Pay
From Salary.xlsx
Isn't that what you asked? The values of jan-march in the q1Pay and apr-june in q2pay? Am I missunderstanding you?