If with Count and Sum

[shwetagupta]

Hi Fellas,

I need to show a list of user visiting to a particular dealer when my visit count >3 and Qty Sold =0.

I am writing this in dimension: =If(Count({<status={'Active'}>}fe_ref_num)>3 and Sum(qty sold)=0, User)

Please suggest me how to show this and what is wrong with the condition stated above.

Thanks in advance

[MK_QSL]

Count({<User = {"=SUM(qty)=0 and count({<status = {'Active'}>}[fe_ref_num])>3"}>}Distinct User)

[micheledenardi]

I suggest to calculate a new field during the load statement in order to have better performance... so:

MainTable_tmp:

Load

     User,

     Field1,

     Field2,

     Field3

from YourSource;

Left Join(MainTable_tmp)

     User,

     Count(if(status='Active',fe_ref_num,0)) as CountFeRef,

     Sum([Qty sold]) as TotQtySold

resident MainTable_tmp

     group by User;

MainTable:

NoConcatenate

Load

     User,

     Field1,

     Field2,

     Field3,

     CountFeRef,

     TotQtySold,

     if(CountFeRef>3 and TotQtySold=0, User) as NewUser

from MainTable_tmp;

Drop table MainTable_tmp;

and then put NewUser field on your charts !

[zhadrakas]

you should use aggr() in Dimensions if you want to use Aggregation functions in a Dimension:

try like

If(aggr(Count({<status={'Active'}>}fe_ref_num), Dealer) >3 and aggr(Sum(qty sold), Dealer) =0, User)

replace "Dealer" with the Dimension you want to Aggregate on.

regards

tim

[antoniotiman]

Hi,

try

=Aggr(If(Count({<status={'Active'}>}fe_ref_num)>3 and Sum(qty sold)=0, User),User)

and check 'Suppress when value null'

Regards,

Antonio

[shwetagupta]

I appreciate your efforts . But in my case both belong to the two different table! I cant combine them at the backend

[shwetagupta]

Hi Tim,

using your above suggestion, It is not taking this condition

Count({<status={'Active'}>}fe_ref_num), Dealer) >3

because I am getting users who are having visit count less than 3

[MK_QSL]

How you Marked answer correct without proper testing?

[MK_QSL]

Try

If(aggr(Count(Total {<status={'Active'}>}fe_ref_num), Dealer) >3 and aggr(Sum(qty sold), Dealer) =0, User)

[shwetagupta]

Yup Mistakenly!