topic Re: Pick Match Value Function not working. in QlikView

Pick Match Value Function not working.

savitakhyadi — Mon, 07 Jan 2019 11:41:34 GMT

Hi All,

I am creating a pivot table where am using pick match value function like below.

pick(match(ValueList('Q1','Q2','Q3','Q4','FY'),'Q1','Q2','Q3','Q4','FY'),
$(vWinrate$q1Test),$(vWinrate$q2Test),$(vWinrate$q3Test),$(vWinrate$q4Test),$(vWinrate$fy18Test))

now problem is its perfectly working fine for FY18 data, but for FY19(I have only Q1 data, its not working.

at least it should get Q1 data for fy19.

Can someone please help me here.

I don know what mistake am doing here.

PS: I have the data for FY19 Q1.

Thanks & Regards

Savita

Re: Pick Match Value Function not working.

sunny_talwar — Mon, 07 Jan 2019 12:19:51 GMT

@savitakhyadi wrote:

at least it should get Q1 data for fy19.

So, right now you are not seeing Q1 FY19 data in your expression? All you see in 2018 data? What are the expressions behind these variables?

$(vWinrate$q1Test),
$(vWinrate$q2Test),
$(vWinrate$q3Test),
$(vWinrate$q4Test),
$(vWinrate$fy18Test)

Re: Pick Match Value Function not working.

savitakhyadi — Mon, 07 Jan 2019 12:23:45 GMT

Yes, I have a FY filter, when i select fy18, i can see the data in pivot table, but when i select fy19, pivot table is blank. Below is the expression, it just I change Q1,Q2,Q3,Q4 values in the expression.Rest all same.

=sum(aggr(max({$<Flag = {'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},
[Fiscal Quarter]=,[Fiscal Month]=,[Last Approved OBR Flag]={'Y'},[Approval Record Type]={'Opportunity Business Review'},[Sales Stage]-={'01 - Understand Customer',
'02 - Validate Opportunity',
'03 - Qualify the Opportunity',
'04A - Develop Solution',
'04B - Propose Solution',
'05 - Negotiate & Close',
'HPE Not Pursued',
'Lost',
'Error',
'Unknown'},
[PN LSR Proportion$]-={} >}[PN LSR Proportion$]),[HPE Opportunity Id],[Approval ID],GBU,[TS Approval GBU]))
/
sum({$<Flag = {'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},
[Fiscal Quarter]=,[Fiscal Month]=,[Last Approved OBR Flag]={'Y'},[Approval Record Type]={'Opportunity Business Review'},[Sales Stage]={'06 - Won, Deploy & Expand','HPE Not Pursued','Lost'}
,[PN LSR Proportion$]-={} >}aggr(max({$<Flag = {'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},
[Fiscal Quarter]=,[Fiscal Month]=,[Last Approved OBR Flag]={'Y'},[Approval Record Type]={'Opportunity Business Review'},[Sales Stage]={'06 - Won, Deploy & Expand','HPE Not Pursued','Lost'}
,[PN LSR Proportion$]-={} >}[PN LSR Proportion$]),[HPE Opportunity Id],[Approval ID],GBU,[TS Approval GBU]))

Re: Pick Match Value Function not working.

sunny_talwar — Mon, 07 Jan 2019 12:32:52 GMT

Not suggesting to use this as your final expression... but can you see if this is working for you

=Sum({1} Aggr(Max({$<Flag = {'Opportunity_Snapshot'}, [Snapshot Quarter] = {'Q1'}, [GBU] = {'Advisory and Professional Services', 'Operational Services'},
[Fiscal Quarter], [Fiscal Month], [Last Approved OBR Flag] = {'Y'},[Approval Record Type] = {'Opportunity Business Review'}, [Sales Stage] -= {'01 - Understand Customer',
'02 - Validate Opportunity',
'03 - Qualify the Opportunity',
'04A - Develop Solution',
'04B - Propose Solution',
'05 - Negotiate & Close',
'HPE Not Pursued',
'Lost',
'Error',
'Unknown'},
[PN LSR Proportion$] -= {}>} [PN LSR Proportion$]), [HPE Opportunity Id], [Approval ID], GBU, [TS Approval GBU]))
/
Sum({$<Flag = {'Opportunity_Snapshot'}, [Snapshot Quarter] ={'Q1'}, [GBU] = {'Advisory and Professional Services', 'Operational Services'},
[Fiscal Quarter], [Fiscal Month], [Last Approved OBR Flag] = {'Y'},[Approval Record Type] = {'Opportunity Business Review'}, [Sales Stage] = {'06 - Won, Deploy & Expand', 'HPE Not Pursued','Lost'}
,[PN LSR Proportion$]-={}>}Aggr(Max({$<Flag = {'Opportunity_Snapshot'}, [Snapshot Quarter] = {'Q1'}, [GBU] ={'Advisory and Professional Services', 'Operational Services'},
[Fiscal Quarter], [Fiscal Month], [Last Approved OBR Flag] = {'Y'},[Approval Record Type] = {'Opportunity Business Review'}, [Sales Stage] = {'06 - Won, Deploy & Expand', 'HPE Not Pursued', 'Lost'}, [PN LSR Proportion$] -={}>} [PN LSR Proportion$]), [HPE Opportunity Id], [Approval ID], GBU, [TS Approval GBU]))

Re: Pick Match Value Function not working.

savitakhyadi — Mon, 07 Jan 2019 12:37:55 GMT

No,its not working, am not getting data in pivot table.

Re: Pick Match Value Function not working.

sunny_talwar — Mon, 07 Jan 2019 12:41:27 GMT

Would you be able to share a sample where we can see the issue?