Set Analysis

[#1]

Hi,

I am trying to build the following set analysis:

I need to display all the "Unique Num"s that registered online

(Source_Name ='Online',

ActionType_Name ='Lead',

Lead_Category ≠ 'renew'\'my leads')

and show what they bought (action type name = sale) according to the user's Date selection.

(the user requested to see only the sales that occurred in the same month that the lead was created).

For example, according to the file attached, I need to display only unique number 2801267 because he 

has registered on July 2^nd, and bought on July 3^rd and his Lead_category is not equal to  'renew'\'my leads'

I tried to build the set analysis, I'm having trouble with the conditions:

=count({<

Unique_Num = P({<Source_Name={"Online"}, ActionType_ID={"Lead"},Lead_Category-=('My Leads','Renew'),Date={">=$(v_MinDate) <=$(v_MaxDate)"}>} Unique_Num),

ActionType_Name={"Sale"},Date={">=$(v_MinDate) <=$(v_MaxDate)"}>} Unique_Num)

[pooja_prabhu_n · Accepted solution]

Hi,

Try this one

=count({<Unique_Num = P({<Source_Name={'Online'},Unique_Num={'=Len(Trim(Lead_Category))=0'}, ActionType_Name={'Lead'},Date={">=$(VMonthStart) <=$(v_MaxDate)"}>} Unique_Num),

ActionType_Name={'Sale'} ,Date={">=$(VMonthStart) <=$(v_MaxDate)"}>} Unique_Num)+

count({<Unique_Num = P({<Source_Name={'Online'}, ActionType_Name={'Lead'},Lead_Category={'*'}-{'My Leads','Renew'},Date={">=$(VMonthStart) <=$(v_MaxDate)"}>} Unique_Num),

ActionType_Name={'Sale'} ,Date={">=$(VMonthStart) <=$(v_MaxDate)"}>} Unique_Num)

[#3]

Perfect!