Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Count({<User = {"=SUM(qty)=0 and count({<status = {'Active'}>}[fe_ref_num])>3"}>}Distinct User)
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 !
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
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
I appreciate your efforts . But in my case both belong to the two different table! I cant combine them at the backend
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
How you Marked answer correct without proper testing?
Try
If(aggr(Count(Total {<status={'Active'}>}fe_ref_num), Dealer) >3 and aggr(Sum(qty sold), Dealer) =0, User)
Yup Mistakenly!