Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Not applicable

## Return a dimension from expression(set analysis)

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

100020000
21000000100000
320000
43000000

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

1 Solution

Accepted Solutions
MVP

Try this:

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

14 Replies
MVP

Try this:

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

Not applicable
Author

Thank you!

MVP

No problem

Not applicable
Author

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

MVP

Not sure what you mean, can you elaborate?

Not applicable
Author

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!

MVP

Would you be able to share a sample?

Not applicable
Author

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.

MVP

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