Aggr with Sum IF only works selecting one variable

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

See if this link helps

Preparing examples for Upload - Reduction and Data Scrambling

[marcus_malinow]

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

[Anonymous]

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.

[jonathandienst]

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

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

Hi Sunny

Thank you for trying

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