14 Replies Latest reply: Jul 13, 2017 7:54 AM by Sunny Talwar

# If  with Count and Sum

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

• ###### Re: If  with Count and Sum

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

• ###### Re: If  with Count and Sum

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 !

• ###### Re: If  with Count and Sum

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

• ###### Re: If  with Count and Sum

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

• ###### Re: If  with Count and Sum

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

• ###### Re: If  with Count and Sum

How you Marked answer correct without proper testing?

• ###### Re: If  with Count and Sum

Try

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

• ###### Re: If  with Count and Sum

Not Working!

It is still showing users with count less than 3.

• ###### Re: If  with Count and Sum

Can you provide sample app or few lines of sample data?

• ###### Re: If  with Count and Sum

Yup Mistakenly!

• ###### Re: If  with Count and Sum

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

• ###### Re: If  with Count and Sum

Did this not work for you ( mrkachhiaimp's suggestion above)

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

• ###### Re: If  with Count and Sum

I need to restrict dimension!

Though If I have used this to checkout the list of the user with count 1.

it is not restricting the user with qty=0 and visit count>3

• ###### Re: If  with Count and Sum

Got it, how about this (as mentioned by Antonio Mancini)

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