topic Aggr with Sum IF only works selecting one variable in QlikView

Aggr with Sum IF only works selecting one variable

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hey everyone

I really hope you can help with this problem.

I have this function which works perfectly when I'm only selecting one order or one customer:

IF(Aggr(sum({$<CAL_YEAR =, CAL_MONTH =,CAL_DAY=, AD_P_DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}>} NET), CUSTOMER) = '0', Sum(IF(CUSTID <> 'IA' And CUSTID <> 'KA' And CUSTID <> 'BA' AND PLACE <> 'BBB', NET)))

However, if I don't select a specific customer or order, nothing happens besides getting - .

Can anyone explain what I'm doing wrong? I have searched around the internet, and I have been able to find some similar problems with a solution. However I haven't been able to implement those solution to my expression.

Stefan

Re: Aggr with Sum IF only works selecting one variable

sunny_talwar — Tue, 13 Jun 2017 08:19:19 GMT

Would you be able to share a sample Stefan? It might be easier to see what you are doing wrong in a sample, then by just looking at an expression

Re: Aggr with Sum IF only works selecting one variable

Anonymous — Tue, 13 Jun 2017 08:24:48 GMT

Hi Sunny

Sorry, but I'm not able to share a sample. Since its part of a larger setup, and i wouldn't know how to create a sample out of that.

Re: Aggr with Sum IF only works selecting one variable

sunny_talwar — Tue, 13 Jun 2017 08:32:21 GMT

See if this link helps

Preparing examples for Upload - Reduction and Data Scrambling

Re: Aggr with Sum IF only works selecting one variable

marcus_malinow — Tue, 13 Jun 2017 08:41:33 GMT

Hi Stefan,

I think you're overcomplicating your expression here, and perhaps not understanding how Aggr works. Is there a specific reason why you are using it in addition to your Sum?

Try this first:

IF(sum({$<CAL_YEAR =, CAL_MONTH =,CAL_DAY=, AD_P_DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}>} NET) = '0', Sum(IF(CUSTID <> 'IA' And CUSTID <> 'KA' And CUSTID <> 'BA' AND PLACE <> 'BBB', NET)))

Possibly your final Sum should be modified to use set analysis:

IF(sum({$<CAL_YEAR =, CAL_MONTH =,CAL_DAY=, AD_P_DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}>} NET) = '0', Sum({$<CUSTID -={'IA', 'KA', 'BA'}, PLACE -= {'BBB'}>}, NET))

Marcus

Re: Aggr with Sum IF only works selecting one variable

Anonymous — Tue, 13 Jun 2017 09:01:20 GMT

Hi Marcus

No there is not a specific reason why I'm using it. I'm kinda new to Qlikview, and all my expressions is just built upon own experience and google search

However, using your expression gives me the same result. When choosing a specific customer/order I receive the correct result. But when not selecting any specific I get none result.

Re: Aggr with Sum IF only works selecting one variable

jonathandienst — Tue, 13 Jun 2017 09:17:19 GMT

I expect that the expression

Sum({$<CAL_YEAR =, CAL_MONTH =, CAL_DAY =, AD_P_DATE={'>=$(=MonthStart(Today(),-12)) <$(=MonthStart(Today()))'}>} NET)

will be greater than 0 unless only zero value items are selected. This expression is evaluated at the level of the dimensions. If the dimensions are not customer/order then I am pretty sure that is the problem. Perhaps If(Sum()) is not the correct construct for your problem. but without a sample, I can only guess at the correct structure. Perhaps this:

Sum(Aggr(

IF(sum({$<CAL_YEAR =, CAL_MONTH =,CAL_DAY=, AD_P_DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}>} NET) = '0', Sum({$<CUSTID -={'IA', 'KA', 'BA'}, PLACE -= {'BBB'}>}, NET))

, Customer, Order, <other table or chart dimensions>))

<other table or chart dimensions> should be replaced by a comma separated list of the chart dimensions other than Customer and Order

Re: Aggr with Sum IF only works selecting one variable

Anonymous — Tue, 13 Jun 2017 09:51:49 GMT

I have tried making a sample, but haven't been able to make an sample which makes sense.

Basically I just want to sum the sales of customers who haven't bought anything last 12 months. So if customer haven't bought anything from June 2016 till May 2017, but buys something in June 2017, I want it to sum.

I made this expressions to count orders made by customers who haven't bought anything the last 12 months.

And this expression works perfectly. So thought I could just substitute some things, and make it sum Sales instead.

Count(DISTINCT IF(CUSTID <> 'IA' And CUSTID <> 'KA' And CUSTID <> 'BA' AND Aggr(sum({$<CAL_YEAR =, CAL_MONTH =, AD_P_DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}>} NET), CUSTOMER) = '0' , OrderNo))

Re: Aggr with Sum IF only works selecting one variable

sunny_talwar — Tue, 13 Jun 2017 10:57:01 GMT

May be this

Count({<Customer = e({<CAL_YEAR =,CAL_MONTH =, AD_P_DATE={">=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))"}, CUSTID -= {'IA', 'KA', 'BA'}>}Customer), CAL_YEAR =,CAL_MONTH =, AD_P_DATE={"$(=MonthStart(Today()))"}, CUSTID -= {'IA', 'KA', 'BA'}>} OrderNo)

Re: Aggr with Sum IF only works selecting one variable

Anonymous — Tue, 13 Jun 2017 11:25:29 GMT

Hi Sunny

Thank you for trying

But I guess your right. It's almost impossible help without a sample.

Re: Aggr with Sum IF only works selecting one variable

sunny_talwar — Tue, 13 Jun 2017 11:48:07 GMT

I guess your issue is common issue of Return/New/Lost customer... see if this thread helps

New/Lost/Returning/Loyal Customers