Return a dimension from expression(set analysis)

[#1]

I'm trying to create a dimension for customer numbers that did not have any sales the previous year.  I'm new to Qlik so please excuse me if this is a stupid question. 

Customer Number	CY Sales	PY Sales
1	1000	20000
2	1000000	100000
3	2000	0
4	300000	0

I tried using an if statement, but it said that it was an invalid dimension. Here's the if statement:

if(sum({<[Year]={$(=max([Year])-1)}>} EXTSHIPAMT)=0,CUSTNUM)

I'm assuming there's a function that does this, I'm just not aware of it.

Any help is appreciated!

Thanks,

James

[sunny_talwar · Accepted solution]

Try this:

=Aggr(If(Sum({<[Year]={$(=Max([Year])-1)}>} EXTSHIPAMT) = 0, CUSTNUM), CUSTNUM)

[#3]

Thank you!

[sunny_talwar]

No problem

[#5]

This is expression is returning Null Values, is there anyway to avoid that from within the expression?

[sunny_talwar]

Not sure what you mean, can you elaborate?

[#7]

It's lumping all other customer into one row. So if I created a table using the dimension you helped me with this i what I would get:

Cust Number     CY Sales     PY Sales

     3                   2000              0

     4                  300000           0

     -                  1001000       120000    

I know I can uncheck the Show Null Values on the dimension, but I was hoping I could accomplish that in the expression.  I'm trying use this to create a KPI, but because it's returning all of the other values my total is way off.  I hope I explained that properly.  Thanks again for your help!   

[sunny_talwar]

Would you be able to share a sample?

[#9]

The first row is where it is lumping the null values together.

I created a measure for the KPI:

sum(if(aggr(sum({<[Year]={$(=max(Year)-1)}>} IF(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT)),[Cust Name])=0,0,IF(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT)))

The measure also sums up the totals for the null values.  Essentially all I want is a ytd total in a KPI for any customer that did not buy anything last year.  The table works for this, but I just like the way the KPI looks. 

[sunny_talwar]

Try this:

Sum({<[Year]={$(=max(Year)-1)}>} If(Aggr(Sum({<[Year]={$(=max(Year)-1)}>} If(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT)),[Cust Name])=0, 0, If(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT)))