Using Pick(RangeSum for 2 variables) Not giving desired results

[helen_pip]

Dear Qlikview user

Yesturday I posted a discussion which was correctly answered.  My problem was that I wanted to use the Pick function instead of IF statements for performance improvement, so I was able to change my expression to the following:

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)

))

However I now need to add in a new variable into the same expression and have been playing around with the combinations of what RangeSum is producing, but my figures do not match the Range Sum and does not show the correct expression at the right combination

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)

))

   Is anyone able to assist me with whether i should be using range sum for this type of expression.  The original is as follows:

If($(v_Selected) = 1 and $(vShow2WW) = 0,

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

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

ID_Number),

If($(v_Selected) = 2 and $(vShow2WW) = 1,

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

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

ID_Number),

If($(v_Selected) = 1 and $(vShow2WW) = 2,

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

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

ID_Number)

))

Any help would be greatly appreciated

Kind Regards

Helen

[swuehl · Accepted solution]

Maybe like

Pick(Match($(vShow2WW)&'-'&$(v_Selected),'1-0','2-1','1-2'),

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 Stefan

Thank you for your suggestion....I was about to embark on putting my variable settings to very randomn numbers so they would never add up to the same amount, but with your help, I can put the combinations in order in the expression

Thanks for your suggestion, this works for me

Kind Regards

Helen