Summing based on Selections

[imrasyed]

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 :

Header 1	Header 2	Header 3
Territory No	Sum(unit sales)	Sum(inventory Units)
1	100	30
2	20	100
3	500	200

Expected Output:

Header 1	Header 2	Header 3
Territory No	Sum(unit sales)	Sum(inventory units)
1 + 2	120	130
3	500	200

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

Really appreciate your kind help

Thanks

Syed Imran

[Anonymous]

May be this,  see attached

[imrasyed]

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 ?

[Anonymous]

​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:

LOAD * INLINE [

Territory No, sales, Units,

1, 100, 30

2, 20, 100

3, 500, 200

];

[imrasyed]

can we do Alternate states in Qliksense?

[Anonymous]

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

[imrasyed]

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.

[Anonymous · Accepted solution]

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]) )

and load script

Data:

LOAD * INLINE [

Territory No,    sales,    Units,

1,                100,    30

2,                20,        100

3,                500,    200

];

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

[imrasyed]

Hi Max,

Awesome

It is working as expected.

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

Can you please elobarate?

Many Many thanks.

[Anonymous]

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.