Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Aggr with Sum IF only works selecting one variable

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

10 Replies
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
Not applicable
Author

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.

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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
Not applicable
Author

Hi Sunny

Thank you for trying

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