Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gayatri7
Creator II
Creator II

Pick Match Function - not ignoring selection in Set Analysis

Hi All,

I have below expression and it is not ignoring the selections on Year and Month Columns.

Pick(
Match(Only({<[Month Name]=, Year=>}KPI_Name), 'Personal Cost', 'T&M'),
SUM({<Account_Id={'1180'},[Month Name]=,NumMonth_Code={"<=$(vMonthNo)"}, Year=>}Amount_m_USD),

SUM({<Account_Id={'1186'},[Month Name]=,NumMonth_Code={"<=$(vMonthNo)"},Year=>}Amount_m_USD)
)

 

if the same expression is written individually (i.e. without Pick -Match function) it is working perfectly fine. It is ignoring the selection made on Year and Month column.

 

Could anyone help me what mistake I am doing in expression.

 

Thanks for your help in advance!

Gayatri

 

Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

I don't see any issue in your pick match which is strange not working. 

Did you try getfieldselections ? It should work as expected

 

if(getfieldselections(KPI_Name)='Personal Cost',

SUM({<Account_Id={'1180'},[Month Name]=,NumMonth_Code={"<=$(vMonthNo)"}, Year=>}Amount_m_USD),

if(getfieldselections(KPI_Name)='T&M',

SUM({<Account_Id={'1186'},[Month Name]=,NumMonth_Code={"<=$(vMonthNo)"},Year=>}Amount_m_USD)
))

View solution in original post

12 Replies
Kushal_Chawda

I don't see any reason that it should not work. Can you please give more information like where you are using this expression? what are other dimensions? Sharing sample file will be helpful to get faster reply

gayatri7
Creator II
Creator II
Author

Hi Kush,

I am using this expression in Bar chart. And I am getting only one bar as year - 2020 is selected. with Pick Match expression

gayatri7_0-1597244449759.png

But when I am writing the expression individually. I am getting all years as it is ignoring the selection.

gayatri7_1-1597244613188.png

Sorry I can't share the sample file as I don't have desktop version installed.

 

Kushal_Chawda

Where is the reference of KPI_Name here?  What is usage of KPI_Name?

Kushal_Chawda

I can see that your both expression are same except  account_id filter. You can try below.

Create variable vKPI_Account_ID on front end with below condition

=if(KPI_Name='Personal Cost',1180,1186)

then you can simply use below expression


SUM({<Account_Id={'$(vKPI_Account_ID)'},[Month Name]=,NumMonth_Code={"<=$(vMonthNo)"}, Year=>}Amount_m_USD)

gayatri7
Creator II
Creator II
Author

KPI_Name is a drop down field  --> with values as Personal Cost and T&M

gayatri7_0-1597245810195.png

 

Thanks

Kushal_Chawda

try what I suggested above

gayatri7
Creator II
Creator II
Author

Thanks for this . But I guess it will not work, as upcoming days I have to include another 10 KPIs. Which all  may have different calculations with different Account ids.

Main concern here is ignore selection is not working in Set Analysis inside Pick & Match function.

I have to create a drop down so that user can select the KPIs they want to see in the front end. so can't go with the expression you suggested at the script level.

 

Kushal_Chawda

I don't see any issue in your pick match which is strange not working. 

Did you try getfieldselections ? It should work as expected

 

if(getfieldselections(KPI_Name)='Personal Cost',

SUM({<Account_Id={'1180'},[Month Name]=,NumMonth_Code={"<=$(vMonthNo)"}, Year=>}Amount_m_USD),

if(getfieldselections(KPI_Name)='T&M',

SUM({<Account_Id={'1186'},[Month Name]=,NumMonth_Code={"<=$(vMonthNo)"},Year=>}Amount_m_USD)
))

gayatri7
Creator II
Creator II
Author

Yeah let me try this option!

Thanks 😊