Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mrainey18
Contributor

Set Analysis - Exclude Selections Except

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis - Exclude Selections Except

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

19 Replies
MVP
MVP

Re: Set Analysis - Exclude Selections Except

So you are looking to ignore everything except SIC? May be this

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

or this

Sum({1<SIC = $:Smiley FrustratedIC>} $(vSales))

YoussefBelloum
Esteemed Contributor

Re: Set Analysis - Exclude Selections Except

hi,

can you attach some some lines of your table ?

mrainey18
Contributor

Re: Set Analysis - Exclude Selections Except

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

or this

Sum({1<SIC = $:Smiley FrustratedIC>} $(vSales))


I think you had an extra }


Either way this comes back blank/null.

mrainey18
Contributor

Re: Set Analysis - Exclude Selections Except

Here is my code:

Sum({1<SICName = $:Smiley FrustratedICName, SIC= $:Smiley FrustratedIC}>} $(vSales))

or

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


Tried both, both returns nulls.

mrainey18
Contributor

Re: Set Analysis - Exclude Selections Except

Unfortunately I can not. 

MVP
MVP

Re: Set Analysis - Exclude Selections Except

Yes, I did have an extra } in both my expression... and so do you... can you try this

Sum({1<SICName = $:Smiley FrustratedICName, SIC= $:Smiley FrustratedIC>} $(vSales))

or

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

mrainey18
Contributor

Re: Set Analysis - Exclude Selections Except

Woopse!  Thank you.  This does show information but its also dividing it by Cost Center

So instead of:

Baltimore                              100,000                          1,000,000

     Clothing                            5500                              200,000

It shows:

Baltimore                              100,000                         100,000

     Clothing                            5500                             5500

mrainey18
Contributor

Re: Set Analysis - Exclude Selections Except

So I change it to CostCenter and that didnt work either:

Sum({1<CostCenterName = $::CostCenterName, CostCenter = $::CostCenter >} $(vSales))

MVP
MVP

Re: Set Analysis - Exclude Selections Except

May be adding total... not entirely sure but may be this

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