efficient expression with an If and variable

[helen_pip]

Dear Qlikview user

I am currently carrying out an exoercise where I am aiming to improve the performance and efficiency of some of my expressions

On one particular page I have numerous expressions set up as follows:

If($(vShow2WW) = 1,

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

data_source = {' published CWT', 'Somerset'}>}

ID_Number),

If($(vShow2WW) = 0,

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

data_source = {' 31 Day published CWT'}>}

ID_Number),

If($(vShow2WW) = 2,

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

data_source = {'62 Day published CWT'}>}

ID_Number)

)))

I don't think I can use Pick(Match with the variables?  I am however thinking of going back to the script and change some of descriptions for Flags

Any help on how to improve performance with this type of expression would be greatly appreciated

Kind Regards

Helen

[sunny_talwar · Accepted solution]

You should be able to use Pick Match... May be this

Pick(RangeSum($(vShow2WW), 1),

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

data_source = {' 31 Day published CWT'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

data_source = {' published CWT', 'Somerset'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

data_source = {'62 Day published CWT'}>}

ID_Number)

))

[helen_pip]

Hi Sunny

Thanks for your help!  This works for me!

Thanks

Helen

[sunny_talwar]

That is great, please close the thread if you got what you wanted.

Best,

Sunny

[helen_pip]

Hi Sunny

I have marked it as correct and thank you for your help, but am now starting to get a little more ambitious and am trying to use RangeSum() with 2 variables

I.e.

Pick(RangeSum($(vShow2WW),$(v_Selected),1),

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

data_source = {' 31 Day published CWT'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

data_source = {' published CWT', 'Somerset'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

data_source = {'62 Day published CWT'}>}

ID_Number)

))

The new variable $(v_Selected) has two options 1 and 2, but the option 2 seems to always get missed out.  Have a written the expression accurately to accomodate a second variable?

Any advice is greatly appreciated

Kind Regards

Helen

[sunny_talwar]

So vShow2WW can equal 0, 1 or 2? and v_Selected can equal 1 or 2? So based on this you have 4 possibilities? Is this right?

0+1 = 1

0+2 = 2

1+1 = 2

1+2 = 3

2+1 = 3

2+2 = 4

May be you need this (Removing , 1 from RangeSum)

Pick(RangeSum($(vShow2WW),$(v_Selected)),

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

data_source = {' 31 Day published CWT'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

data_source = {' published CWT', 'Somerset'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

data_source = {'62 Day published CWT'}>}

ID_Number)

))

[helen_pip]

Hi Sunny

Yes, you are right, they are the combinations. I have taken off the 1, but cannot get an exact match. I am also playing around with different scenarios

I.e. Pick(RangeSum($(v_Selected),$(vShow2WW),0,1) to see if the range works

Thanks for your suggestion though...I will have a little play about with it!