topic Qlik sense - Set Analysis - Sum ignoring one dimension in App Development

Qlik sense - Set Analysis - Sum ignoring one dimension

agarciaguillen — Sun, 17 Sep 2017 16:18:22 GMT

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

Oleg_Troyansky — Sun, 17 Sep 2017 19:14:09 GMT

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

agarciaguillen — Mon, 18 Sep 2017 19:24:55 GMT

Really thanks, you answer was helpful to me

Regards