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

helen_pip — Wed, 26 Jul 2017 08:01:37 GMT

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

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

swuehl — Wed, 26 Jul 2017 09:18:00 GMT

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)

))

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

helen_pip — Wed, 26 Jul 2017 09:27:04 GMT

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

topic Re: Using Pick(RangeSum for 2 variables) Not giving desired results in QlikView

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

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

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