topic Re: Having clause in an expression in QlikView

Having clause in an expression

Anonymous — Tue, 21 Oct 2014 17:12:25 GMT

Hi guys,

I am trying to find distinct customers who have a value of spend more than zero within a range.

My fields are called CustNo and val and the state is called Range 1 (or Range 2).

Is this possible in an expression?

Thanks for your help as always

Re: Having clause in an expression

JonnyPoole — Tue, 21 Oct 2014 17:18:24 GMT

Having in SQL is like filtering after aggregation

An example will help. Depending on the context you can do subtotals with sum ( TOTAL <expression> ) or using aggr( sum( <expression> ) , <Dimension>) .

You can use an IF() statement with the above to filter for a range.

For an example , attach a QVW sample to your post using the 'advanced editor' in your reply.

Re: Having clause in an expression

Tue, 21 Oct 2014 17:22:54 GMT

try using set analysis like this one

sum({$< CustNo ={>'Range 1'<'Range 2'}>} CustNo)

Re: Having clause in an expression

Anonymous — Wed, 22 Oct 2014 10:35:37 GMT

Sorry, i probably didn't explain what i am trying to do very well.

Basically the expression =Count(DISTINCT CustNo) works perfect. But i now want to do a count on distinct CustNo where the value spent is more than zero. The value field is just called val..

Help!

Re: Having clause in an expression

ashfaq_haseeb — Wed, 22 Oct 2014 10:40:38 GMT

Hi,

Try this

Count({<val={'>0'}>}DISTINCT CustNo)

Regards

ASHFAQ

Re: Having clause in an expression

jyothish8807 — Wed, 22 Oct 2014 10:45:40 GMT

Hi Jamel,

Try this:

Count({<val={">0"}>}DISTINCT CustNo)

What is state in your case (Range1). Is it connected to your requirement?

Regards

Re: Having clause in an expression

its_anandrjs — Wed, 22 Oct 2014 10:59:52 GMT

Try some thing like

Count({<val={">0"}, state ={ 'Range 1' , 'Range 2' } >}DISTINCT CustNo)

Regards,

Anand

Re: Having clause in an expression

Anonymous — Wed, 22 Oct 2014 11:04:20 GMT

Hi guys,

thanks for your help.

It is still counting a customer when their spend is less than zero though.. Arggghh

Re: Having clause in an expression

anbu1984 — Wed, 22 Oct 2014 11:06:40 GMT

Can you post sample qvw

Re: Having clause in an expression

ashfaq_haseeb — Wed, 22 Oct 2014 11:09:22 GMT

Hi,

Try this

=Count({<CustNo={"=sum(val)>0"}>} distinct CustNo)

Let me know if that worked.

Regards

ASHFAQ

Re: Having clause in an expression

Anonymous — Wed, 22 Oct 2014 11:10:10 GMT

Is it because I need to use the "sum" of the value?

Re: Having clause in an expression

ashfaq_haseeb — Wed, 22 Oct 2014 11:11:40 GMT

did you try this?

=Count({<CustNo={"=sum(val)>0"}>} distinct CustNo)

Regards

ASHFAQ

Re: Having clause in an expression

Anonymous — Wed, 22 Oct 2014 11:13:16 GMT

Yeeeeeees!! ! That worked. Thanks very much Ashfaq I knew i was going wrong somewhere...

Re: Having clause in an expression

ashfaq_haseeb — Wed, 22 Oct 2014 11:13:58 GMT

Welcome

Regards

ASHFAQ

Re: Having clause in an expression

Anonymous — Wed, 22 Oct 2014 11:18:26 GMT

Just want to go one further.. Haha. Is it possible to group by these customers as i am still seeing the customers within the last but they are not being counted (which is good!) but i would like to not see them in the table..

I know i am a perfectionist!

Re: Having clause in an expression

anbu1984 — Wed, 22 Oct 2014 11:25:54 GMT

Change your value expression to

If(Column(1)<>0,Value_expression)

Column(1) returns value of first expression

Re: Having clause in an expression

ashfaq_haseeb — Wed, 22 Oct 2014 11:26:19 GMT

Hi,

Add calculated dimension if (sum(Val)>0,CustNo)

Regards

ASHFAQ

Re: Having clause in an expression

Anonymous — Wed, 22 Oct 2014 11:28:21 GMT

Brilliant !! Thanks Anbu that's worked a treat..

Re: Having clause in an expression

Anonymous — Wed, 22 Oct 2014 14:54:53 GMT

Hi guys,

i am trying to take this a step even further and compare years which i have created variables for.

This expression doesn't work though

=COUNT({<CalendarYear ={$(vyear1)}>}{<CustNo={"=sum(val)>0"}>} Distinct left(Customer,5))

If i change the year within the variable box the data stays the same!

Re: Having clause in an expression

anbu1984 — Thu, 23 Oct 2014 05:14:51 GMT

=COUNT({<CalendarYear ={$(vyear1)},CustNo={"=sum(val)>0"}>} Distinct left(Customer,5))