topic Ignoring fields in sum aggr does not work (chart) in App Development

Ignoring fields in sum aggr does not work (chart)

ZimaBlue — Mon, 18 Apr 2022 06:30:45 GMT

Hello!

I'm just trying to do a very simple thing: I need to count the sum of the values so that the filter for some fields is ignored. At the same time, I use aggregation of the sum across several fields.

However, qlik sense ignores this condition and in any case the amount changes when there are fields in the filter that are not specified in the formula.

Data sample:

partner	campaign	adGroup	keyword	spent_eur
p1	c1	ad1	keyword1	15
p1	c2	ad2	keyword2	3
p3	c3	ad3	keyword3	5
p2	c4	ad4	keyword2	10

Let's assume I'm filtering p1 and keyword2. What qlik sense should show me: 18 (15+3) -> (I want to ignore the keyword filter)

What is it really showing me: 3

The formula I use (one of the many options I've tried):

SUM(AGGR(SUM({1 <partner=P(partner_name), campaign=P(campaign), adGroup=P(adGroup), m_date= {">=$(vDateFrom)<=$(vDateTo)"}, keyword=>} spent_eur),
partner, campaign, adGroup, m_date))

I also tried adding total before distinct or after it, but then the value even without the filter becomes incorrect, it's some random incorrect number.

Thanks for any tips

Re: Ignoring fields in sum aggr does not work (chart)

vinieme12 — Mon, 18 Apr 2022 06:49:49 GMT

to ignore a filter in a particular field just leave the right side of equal sign empty, aggr() is not needed

SUM({<keyword=,m_date= {">=$(vDateFrom)<=$(vDateTo)"}>} spent_eur)

Re: Ignoring fields in sum aggr does not work (chart)

ZimaBlue — Mon, 18 Apr 2022 06:53:40 GMT

I use aggr for other purposes, unfortunately I can't give it up.

Re: Ignoring fields in sum aggr does not work (chart)

vinieme12 — Mon, 18 Apr 2022 07:01:54 GMT

Try below

SUM({<keyword=>} AGGR(SUM({<keyword=,- m_date= {">=$(vDateFrom)<=$(vDateTo)"}>} spent_eur),
partner, campaign, adGroup, m_date))

Re: Ignoring fields in sum aggr does not work (chart)

ZimaBlue — Mon, 18 Apr 2022 07:14:04 GMT

It looks like it's working brilliantly! Thank you!