Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shwetagupta
Contributor

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

Tags (1)
14 Replies

Re: If with Count and Sum

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

micheledenardi
Valued Contributor

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 !

zhadrakas
Valued Contributor

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

antoniotiman
Honored Contributor III

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

shwetagupta
Contributor

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

shwetagupta
Contributor

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)

shwetagupta
Contributor

Re: If with Count and Sum

Yup Mistakenly!