Pick match with Less than , greater than or equal to

[helen_pip]

Dear Qlikview user

I have a statement which I want to streamline and potentially improve performance so instead using IF I have opted for Pick(Match

I have read that the sign function could be incorporated into Pick(Match( statements for when the expression is looking for a less than, greater than or equal to outcome

My expression is as follows, and I require some assistance with regards to getting the statement working for the Pick(Match scenario

=Pick(Match(sign(Num(sum({<WEEKS_WAIT = {'<18'}>}COUNTER)/sum(COUNTER),'0.00%')

,Num(sum({$<Week_Number,Month,QuarterName,FiscalYear,Month_Year_Num=P(FactDateNum_prevMonth2),WEEKS_WAIT ={'<18'}>}COUNTER)/

sum({$<Week_Number,Month,QuarterName,FiscalYear,Month_Year_Num=P(FactDateNum_prevMonth2)>}COUNTER),'0.00%')),-1.0,1),

chr(9660), chr(9658), chr(9650))

My expression is basically stating is the Expression is less than previous year then put the relevant character

The sign part is -1,0,1 (Less than, equal to or greater than)

Any help would be greatly appreciated

Kind Regards

Helen

[jmvilaplanap]

Hi,

The sign function has only one parameter, you're trying to put three.

What do you want to compare? What is the original expression?

Regards

[jonathandienst]

I think its simpler than that. You don't need a Match, just add 2 to the output of Sign(), so it returns 1, 2, 3. Like this:

=Pick(Sign(Sum({<WEEKS_WAIT = {'<18'}>} COUNTER) / sum(COUNTER)) + 2, chr(9660), chr(9658), chr(9650))

Replace the bold text with the correct expression to evaluate to +, 0 or -.

[helen_pip]

Hello Jonathan

Thank you for responding, but am unsure where to put the expression which I am supposed to be comparing the expression 2

I.e. =Pick(Sign(Sum({<WEEKS_WAIT = {'<18'}>} COUNTER) / sum(COUNTER)) + 2, chr(9660), chr(9658), chr(9650))

This is expression gives me the current position, but where do I place the expression which evaluates the previous year?

Num(sum({$<Week_Number,Month,QuarterName,FiscalYear,Month_Year_Num=P(FactDateNum_prevMonth2),WEEKS_WAIT ={'<18'}>}COUNTER)/

sum({$<Week_Number,Month,QuarterName,FiscalYear,Month_Year_Num=P(FactDateNum_prevMonth2)>}COUNTER),'0.00%')),

Any help is appreciated

Thanks

Helen

[jmvilaplanap · Accepted solution]

Do you want to know if one is greater than the another? Then you don't need to use Num function.

You can substract one from another

Pick(Sign(ActualYear - PreviousYear) + 2, chr(9660), chr(9658), chr(9650))

ActualYear and PreviousYear are the expressions you have

[helen_pip]

Hello Jose

With not much testing this does seem to be working (Testing to follow)

I want to know if ActualYear is greater than, less than or equal to PreviousYear

Can I kindly ask what the 2 is doing?

Kind Regards

Helen

[jmvilaplanap]

Hi

The two is a great idea from Jonathan.

The sign function returns -1, 0 or 1 depending the sign of the formula inside, and Pick needs a number greater or equal to 1, then if you sum 2 to the result of the sign function, you will have 1, 2 or 3 and this will be that the pick function get.

Regards

[helen_pip]

Thank you for your time Jose, I now understand the concept and can apply with confidence

Thanks for your help

Helen

[helen_pip]

Thank you for your time to reply to the post

With Jose and your help, I was able to get to the correct answer

Thanks

Helen