2 Replies Latest reply: Sep 18, 2017 3:24 PM by Alvaro Garcia

Qlik sense - Set Analysis - Sum ignoring one dimension

Hi everyone, this weekend im working on a report to obtain % of participation of each combination of zone and type of product in all sales channels.

To this i need obtain the subtotal or sum all cambinations of zone and product by all sales channels and  after that divide this by sum of complete combination of channel, zone and product.

I tried to use this:

```Sum({<[Sales Channel]={'Channel1','Channel2','Channel3','Channel4'}>}[Quantity])
```

But this expression sum the same values like

```Sum([Quantity])
```

And if i put the word Totals

```Sum(Totals{<[Sales Channel]={'Channel1','Channel2','Channel3','Channel4'}>}[Quantity])
```

Qliksense calculate the full total without discrimination of zone or product

I upload an example to what i want to obtain.

• Column D =  Sum of Column C and F
• Column E =  Column C / Column D
• Column G = Sum of Column C and F
• Column H = Column F / Column G

Is possible obtain this values ?

Thanks in adavance.

• Re: Qlik sense - Set Analysis - Sum ignoring one dimension

Hi Alvaro,

in your case, you don't need to use Set Analysis at all. Your chart has 3 Dimensions - Channel, Zone, and Product, and you need to calculate total by Zone and Product, disregarding Channel. The syntax is simple, like this:

sum( TOTAL <Zone, Product> Quantity)

If you'd like to learn more about Set Analysis, AGGR, TOTAL, and other advanced techniques, I'm inviting you to listen to my lecture at the Masters Summit for Qlik, or to read my book QlikView Your Business.

Cheers,

Oleg Troyansky

• Re: Qlik sense - Set Analysis - Sum ignoring one dimension

Really thanks, you answer was helpful to me

Regards