Set Analysis - Exclude Selections Except

[mrainey18]

Two things:

1) We recently upgraded to the latest release of Qlik Sense and we have and some problem with the Set Analysis doing the same thing from 3.2 to now.  Were there any big changes to how Qlik Sense reads the Set Analysis code?

2) I know how to "Include all selections except":

     sum( {<CostCenterName= , CostCenterID= >} $(vSales))

Is there a way to do it the other way?

     sum( total {<SIC= >} $(vSales))

That doesn't work.

I have a Pivot Table with Rows of CostCenterName and the SIC.  Measure is sum($(vSales)).

I want to add another measure finding the percentage of sales that cost center does for each SIC they sell towards.

Example:

Cost Center: Baltimore

SIC: Clothing

Sales for Baltimore: 100k

Sales for Baltimore Clothing: 5500

Sales for Clothing: 200k

Total Sales: 10M

Baltimore Total %: 1%

Baltimore Clothing %: 2.75%

Displayed Like this in a pivot table:

Baltimore                              100,000                         1%

     Clothing                            5500                              2.75%

Message was edited by: Michael Rainey Added Test Output

[mrainey18]

Tried it, that ignores everything and displays the total.

[sunny_talwar · Accepted solution]

Not sure, but you may need something like this

Sum(TOTAL <[Cost Center]> {1<SICName = p(SICName), SIC= p(SIC)>} $(vSales))

or

Sum(TOTAL <[Cost Center], SIC> {1<SICName = p(SICName), SIC= p(SIC)>} $(vSales))

or

Sum(TOTAL <SIC> {1<SICName = p(SICName), SIC= p(SIC)>} $(vSales))

[JustinDallas]

Can you give us a dummy load script that has different data, but still represents the problem correctly?

[Anil_Babu_Samineni]

If your question answered, Please flag proper correct answer? Even, This should do your job

Sum({1<[$(=Concat({1<$Field-={'SIC', 'SICName'}>} DISTINCT $Field, ']=,[')&']=')>} $(vSales))

[mrainey18]

I can, but that's going to take me a bit to try and reproduce this exact problem.

[mrainey18]

Nope still not answered, and this one doesn't work either.

If I run a script before hand and do sum(Sales) group by SIC I get what I want, but for all the data.  I need it to be per the selected time.

Not sure why these aren't working.

[sunny_talwar]

Did any of these take you anywhere?

[mrainey18]

No, they return the same thing sum($(vSales)) does.

[mrainey18]

Change one of these around a little bit and got it to work:

Sum(TOTAL <SICName> {1<SICName = p(SICName)>} $(vSales))

[sunny_talwar]

Yes, that was always going to be an issue without having a full look at a sample. I was only trying to help you get closer to your final solution, but I needed you to make changes as you would need. I am glad you were able to finally get a solution which worked for you

Best,

Sunny