Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Tags (1)
1 Solution

Accepted Solutions

Re: Return a dimension from expression(set analysis)

Try this:

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

14 Replies

Re: Return a dimension from expression(set analysis)

Try this:

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

Not applicable

Re: Return a dimension from expression(set analysis)

Thank you!

Re: Return a dimension from expression(set analysis)

No problem

Not applicable

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?

Not applicable

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?

Not applicable

Re: Return a dimension from expression(set analysis)

New Account Table Capture.JPG

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

Community Browser