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

[Anonymous]

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

[ashfaq_haseeb]

did you try this?

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

Regards

ASHFAQ

[Anonymous]

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

[ashfaq_haseeb]

Welcome

Regards

ASHFAQ

[Anonymous]

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!

[anbu1984]

Change your value expression to

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

Column(1) returns value of first expression

[ashfaq_haseeb]

Hi,

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

Regards

ASHFAQ

[Anonymous]

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

[Anonymous]

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!

[anbu1984]

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