14 Replies Latest reply: Jan 26, 2016 4:39 PM by James Parham

# 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

• ###### Re: Return a dimension from expression(set analysis)

Try this:

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

• ###### Re: Return a dimension from expression(set analysis)

Thank you!

• ###### Re: Return a dimension from expression(set analysis)

No problem

• ###### Re: Return a dimension from expression(set analysis)

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

• ###### Re: Return a dimension from expression(set analysis)

Not sure what you mean, can you elaborate?

• ###### Re: Return a dimension from expression(set analysis)

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!

• ###### Re: Return a dimension from expression(set analysis)

Would you be able to share a sample?

• ###### Re: Return a dimension from expression(set analysis)

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.

• ###### Re: Return a dimension from expression(set analysis)

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

• ###### Re: Return a dimension from expression(set analysis)

It still summed up the null values.

• ###### Re: Return a dimension from expression(set analysis)

What if you try this for me:

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

• ###### Re: Return a dimension from expression(set analysis)

Same thing.

• ###### Re: Return a dimension from expression(set analysis)

You won't be able to share a sample?

• ###### Re: Return a dimension from expression(set analysis)

I'm actually not sure how to share a sample.