Having clause in an expression

[Anonymous]

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

[JonnyPoole]

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.

[#3]

try using set analysis like this one

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

[Anonymous]

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!

[ashfaq_haseeb]

Hi,

Try this

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

Regards

ASHFAQ

[jyothish8807]

Hi Jamel,

Try this:

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

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

Regards

KC

[its_anandrjs]

Try some thing like

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

Regards,

Anand

[Anonymous]

Hi guys,

thanks for your help.

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

[anbu1984]

Can you post sample qvw

[ashfaq_haseeb · Accepted solution]

Hi,

Try this

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

Let me know if that worked.

Regards

ASHFAQ