topic Re: How to show the sum of a column for different alternate states in one Expression in QlikView

How to show the sum of a column for different alternate states in one Expression

bvssudhakar — Wed, 25 Nov 2020 16:16:04 GMT

Hello Community,

Here, I am having a Query:

I have 6 alternate states named as set1, set2, set3, set4, set5, set6 (These 6 are Current Selection boxes i.e i used state name function) and i have 4 list boxes named as year, Category, Product name, Customer (Now these 4 are with another alternate state named as 'proposed').

Now i have created one straight table with these columns year, Category, Product name, Customer, Sum(Quantity).

But, i want to show the values when we pass our selections to that sets those values only i want to see.

for that i used sum({Set1 + Set2 + Set3 + Set4 + Set5 + Set6} Quantity). Here, also i am getting problem.. when i pass the selections to all sets that time only it is giving correct result.

My requirement is if i pass my selections to only set1 then also i wnat correct result / if pass the selections to 2 or 3 sets then that time also i want correct result

For that How can i write my expression can you give me the suggestion apart from i used these below expressions also:

=sum({<[Sales Person]=Set1 ::[Sales Person],Customer=Set1 ::Customer>} Quantity)

+sum({<[Sales Person]=Set2 ::[Sales Person],Customer=Set2 ::Customer>} Quantity)

+sum({<[Sales Person]=Set3 ::[Sales Person],Customer=Set3 ::Customer>} Quantity)

+sum({<[Sales Person]=Set4 ::[Sales Person],Customer=Set4 ::Customer>} Quantity)

+sum({<[Sales Person]=Set5 ::[Sales Person],Customer=Set5 ::Customer>} Quantity)

=sum({<[Sales Person]=Set1 ::[Sales Person],Customer=Set1 ::Customer>} Quantity)

&sum({<[Sales Person]=Set2 ::[Sales Person],Customer=Set2 ::Customer>} Quantity)

&sum({<[Sales Person]=Set3 ::[Sales Person],Customer=Set3 ::Customer>} Quantity)

&sum({<[Sales Person]=Set4 ::[Sales Person],Customer=Set4 ::Customer>} Quantity)

&sum({<[Sales Person]=Set5 ::[Sales Person],Customer=Set5 ::Customer>} Quantity)

=num(sum({(vFilterStates)} Quantity)) ---------------(vFilterStates is the variable which i created)

(This is not giving any result)

=Sum({(vFilterStates)} Quantity)

(This is also not giving any result)

For "vFilterStates" Variable i used this expression

= If( len(GetCurrentSelections('','',1,'Set1'))

+ len(GetCurrentSelections('','',1,'Set2'))

+ len(GetCurrentSelections('','',1,'Set3'))

+ len(GetCurrentSelections('','',1,'Set4'))

+ len(GetCurrentSelections('','',1,'Set5'))

+ len(GetCurrentSelections('','',1,'Set6'))

=0, '$',

if(len(GetCurrentSelections('','',1,'Set1'))>0, 'Set1+','')

& if(len(GetCurrentSelections('','',1,'Set2'))>0, 'Set2+','')

& if(len(GetCurrentSelections('','',1,'Set3'))>0, 'Set3+','')

& if(len(GetCurrentSelections('','',1,'Set4'))>0, 'Set4+','')

& if(len(GetCurrentSelections('','',1,'Set5'))>0, 'Set5+','')

& if(len(GetCurrentSelections('','',1,'Set6'))>0, 'Set6+',''))

Can you guys how to show the sum of a column for different alternate states in qlikviewplease help me out from this

Re: How to show the sum of a column for different alternate states in one Expression

sunny_talwar — Wed, 13 Jun 2018 11:29:18 GMT

Would you be able to share a sample to show the issue?

Re: How to show the sum of a column for different alternate states in one Expression

bvssudhakar — Wed, 13 Jun 2018 11:40:46 GMT

Hi Sunny,

Heare i attached sample .qvw file

Re: How to show the sum of a column for different alternate states in one Expression

sunny_talwar — Wed, 13 Jun 2018 11:49:31 GMT

Check if attached is what you want

Re: How to show the sum of a column for different alternate states in one Expression

bvssudhakar — Wed, 13 Jun 2018 12:41:04 GMT

Hi Sunny,

If i use that formula here i am getting like this (as i attached screenshot)

Note: if i am not selecting any thing also it is showing same thing

and i am using Qlikview 11.20 version (personal edition)----- previously i used Qlikview 12.20 version but in this version that formula is showing error to correct that i uninstalled that version and installed 11.20 version then also it is not showing any result.

can you share me the screenshot what you got for each set

Re: How to show the sum of a column for different alternate states in one Expression

sunny_talwar — Wed, 13 Jun 2018 12:49:15 GMT

I am getting this

Where vFilter is this

= If( len(GetCurrentSelections('','','',100,'Set1'))

+ len(GetCurrentSelections('','','',100,'Set2'))

+ len(GetCurrentSelections('','','',100,'Set3'))

+ len(GetCurrentSelections('','','',100,'Set4'))

+ len(GetCurrentSelections('','','',100,'Set5'))

+ len(GetCurrentSelections('','','',100,'Set6'))

=0, '$',

if(len(GetCurrentSelections('','','',100,'Set1'))>0, 'Set1+0','')

& if(len(GetCurrentSelections('','','',100,'Set2'))>0, 'Set2+0','')

& if(len(GetCurrentSelections('','','',100,'Set3'))>0, 'Set3+0','')

& if(len(GetCurrentSelections('','','',100,'Set4'))>0, 'Set4+0','')

& if(len(GetCurrentSelections('','','',100,'Set5'))>0, 'Set5+0','')

& if(len(GetCurrentSelections('','','',100,'Set6'))>0, 'Set6+0',''))

With this expression

=Sum({$(vFilterStates)} Quantity)

Re: How to show the sum of a column for different alternate states in one Expression

bvssudhakar — Wed, 13 Jun 2018 13:06:47 GMT

I opened in my system it is showing error in expression for variable also

can you guess/expect something about this error because i am not able find why i am getting this error.

Re: How to show the sum of a column for different alternate states in one Expression

sunny_talwar — Wed, 13 Jun 2018 13:11:05 GMT

Which SR are you using for QV11.2? Is it before SR8? If you are, then you will need to move to SR8 or above

Re: How to show the sum of a column for different alternate states in one Expression

bvssudhakar — Wed, 13 Jun 2018 13:19:15 GMT

This is the screen shot of qlikview version, here i am not able to find SR version

can you tell me which version it is

Re: How to show the sum of a column for different alternate states in one Expression

sunny_talwar — Wed, 13 Jun 2018 13:21:22 GMT

Def. something before SR7 based on this discussion

upgrade from 11.20.11643.0 to SR7

Re: How to show the sum of a column for different alternate states in one Expression

bvssudhakar — Wed, 13 Jun 2018 13:32:58 GMT

Ok i will upgrade my SR and i will try

Then i will get back to you what result i am getting

Re: How to show the sum of a column for different alternate states in one Expression

bvssudhakar — Wed, 13 Jun 2018 13:35:44 GMT

Thank you for your help and suggestion

Re: How to show the sum of a column for different alternate states in one Expression

sunny_talwar — Wed, 13 Jun 2018 13:35:52 GMT

Sounds good