Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
May be this, see attached
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 ?
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
];
can we do Alternate states in Qliksense?
Oh, I'm not a Qlik Sense user but as far as I know there should an extension for it.
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.
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;
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.
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.