13 Replies Latest reply: Aug 20, 2018 10:24 AM by Imran Syed

Summing based on Selections

Hi Experts,

I have a straight table with Territory Numbers and some Measures like Sum(unit sales),Sum(inventory Units).

Now I need to sum the Total based on selection and simultaneously display entire Table.

Ex:

My  straight Table is :

Territory NoSum(unit sales)Sum(inventory Units)
110030
220100
3500200

Expected Output:

Territory NoSum(unit sales)Sum(inventory units)
1 + 2120130
3500200

How can we achieve this dynamically i.e up on selection it has to sum up and also display other rows

Thanks

Syed Imran

• Re: Summing based on Selections

May be this,  see attached

• Re: Summing based on Selections

Hi Max,

Thanks for the reply but i am not able to see the Data.It is showing unavailable.

Can you please explain what is the concept ?

• Re: Summing based on Selections

Well, You need a calculated dimension  "Territory No"

=If( not WildMatch([Territory No], \$(=chr(39) & Concat({q} DISTINCT [Territory No], chr(39) &chr(44)& chr(39))& chr(39)) ), [Territory No], If(GetSelectedCount([Territory No], ';',  'q') > 0, '\$(=Concat({q}DISTINCT [Territory No], ' + ' ))', [Territory No]) )

expressions:    Sum(sales)    and  Sum(Units)

and a listbox [Territory No] in aletrnate state "q".

Data:

Territory No, sales, Units,

1, 100, 30

2, 20, 100

3, 500, 200

];

• Re: Summing based on Selections

can we do Alternate states in Qliksense?

• Re: Summing based on Selections

Oh, I'm not a Qlik Sense user but as far as I know there should an extension for it.

https://github.com/q2g/q2g-ext-alternatestates

• Re: Summing based on Selections

Yes but i dont want to use extension.

Anyways thanks a lot for the help and will update once I get any alternative to work.

• Re: Summing based on Selections

Then you can try this dimension without alternate state and instead of [Territory No] listbox use [\$Territory No]

=If( not WildMatch([Territory No], \$(=chr(39)&Concat(DISTINCT [\$Territory No], chr(39) &chr(44)& chr(39))& chr(39)) ), [Territory No], If(GetSelectedCount([\$Territory No]) > 0, '\$(=Concat(DISTINCT [\$Territory No], ' + ' ))', [Territory No]) )

Data:

Territory No,    sales,    Units,

1,                100,    30

2,                20,        100

3,                500,    200

];

[Territory No]: LOAD [Territory No] as [\$Territory No] Resident Data;

• Re: Summing based on Selections

Hi Max,

Awesome

It is working as expected.

But was trying to understand the expression but could not get it.

Many Many thanks.

• Re: Summing based on Selections

I'll try))

The result of this part of the expression

\$(=chr(39)&Concat(DISTINCT [\$Territory No], chr(39) &chr(44)& chr(39))& chr(39))

is a string like '1','2' (selected values of [\$Territory No]). And this part '\$(=Concat(DISTINCT [\$Territory No], ' + ' ))'

gives a string 1 + 2 (the same selected values of [\$Territory No]) . So we have something lilke this

=If( not WildMatch([Territory No], '1','2') , [Territory No], '1 + 2')

It means,  when  [Territory No] = '1' and  '2' then it must be 1 + 2.

And I used  this

If(GetSelectedCount([\$Territory No]) > 0,...

for case when [\$Territory No] has no selection and  we would see 1 + 2 + 3 instead of simple list.

• Re: Summing based on Selections

Hi Max,

Can we achieve this in multiple rows using different combinations like

1

2+3

4

5

6+7

Thanks for u r kind help

• Re: Summing based on Selections

Sorry Imran but I can't think of anything interesting.  I'll let you know when I get some.

• Re: Summing based on Selections

No issues Max.