topic Return a dimension from expression(set analysis) in App Development

Return a dimension from expression(set analysis)

Fri, 22 Jan 2016 14:46:23 GMT

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

Re: Return a dimension from expression(set analysis)

sunny_talwar — Fri, 22 Jan 2016 14:48:58 GMT

Try this:

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

Re: Return a dimension from expression(set analysis)

Mon, 25 Jan 2016 13:56:27 GMT

Thank you!

Re: Return a dimension from expression(set analysis)

sunny_talwar — Mon, 25 Jan 2016 14:00:29 GMT

No problem

Re: Return a dimension from expression(set analysis)

Tue, 26 Jan 2016 15:04:53 GMT

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)

sunny_talwar — Tue, 26 Jan 2016 18:22:04 GMT

Not sure what you mean, can you elaborate?

Re: Return a dimension from expression(set analysis)

Tue, 26 Jan 2016 19:26:57 GMT

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)

sunny_talwar — Tue, 26 Jan 2016 19:52:38 GMT

Would you be able to share a sample?

Re: Return a dimension from expression(set analysis)

Tue, 26 Jan 2016 20:15:41 GMT

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)

sunny_talwar — Tue, 26 Jan 2016 20:38:08 GMT

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)

Tue, 26 Jan 2016 20:47:51 GMT

It still summed up the null values.

Re: Return a dimension from expression(set analysis)

sunny_talwar — Tue, 26 Jan 2016 21:09:50 GMT

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)

Tue, 26 Jan 2016 21:31:39 GMT

Same thing.

Re: Return a dimension from expression(set analysis)

sunny_talwar — Tue, 26 Jan 2016 21:38:17 GMT

You won't be able to share a sample?

Re: Return a dimension from expression(set analysis)

Tue, 26 Jan 2016 21:39:53 GMT

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