Pick Match Value Function not working.

[savitakhyadi]

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

[sunny_talwar]

---

@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)

[savitakhyadi]

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]))

[sunny_talwar]

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]))

[savitakhyadi]

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

[sunny_talwar]

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