why getting different result from if and pick(match())

[Huiying]

Hei, I spent several hours experimenting, but still don't get a clue. Can you help?

To improve performance, I'm replacing if statement by pick(match()). Below if statement is the original expression. The pick(match()) function is what I wrote.

Using If statement, I get whole data from 2015-2018. But using the pick(match()) function, I only got 2017-2018.

But there shouldn't be difference between if and pick(match()), right?

=pick(match(%FCOMonthlyReportingTimeId,'P')+1, sum( { < %TimeSelection = > } R_Spend_POData * CurrencyRate ) / 1000,'')

= If( %FCOMonthlyReportingTimeId = 'P',
'',
sum( { < %TimeSelection = > } R_Spend_POData * CurrencyRate ) / 1000
)

[sunny_talwar · Accepted solution]

So, regardless of selection... you are getting different outputs between the two expression? If I may ask, why do you need if or Pick(match()) in the first place... can you just try this

Sum({<%TimeSelection, %FCOMonthlyReportingTimeId -= {'P'}>} R_Spend_POData * CurrencyRate)/1000

[sathishbabu_s_g · Accepted solution]

Hi Susan,

Set Expression gives better performance compared to using IF-Else and Pick&Match Function. you can simply bring the TimeId into the Set expression. 

sum( {$ < %TimeSelection = , %FCOMonthlyReportingTimeId  -= {'P'}> } R_Spend_POData * CurrencyRate ) / 1000

as per your query ideally both should give the same answer, may be share the qvw with sample data if you still wanted to crack what happening in the background. 

[Huiying]

😄 thank you Sunny!

And I now find out why I got different result. If I use only({1} condition) for the match function, then there will be no missing values. 🙂