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)

[Anil_Babu_Samineni]

=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)

May be parenthesis issue like i high lighted

Try this?

=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)

[#3]

It not working....

[balar025]

Hi,

I have made changes in script and your expression.

Please find the attachment.

[vinieme12]

u are referring to a field that does not exist   ActionType_ID

=count({<

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

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

[vinieme12]

should 4 be the correct answer? then use the below expression

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

[#7]

Thank you,

I made some changes to my example. Please see the attached sample

The result that I want to display is 2 for unique_num 12345 and 2801267

Thanks in advance

[pooja_prabhu_n]

Hi ,

I have modified your expression.

=count({<

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

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

The above query will result in only one unique_num 2801267.

Please refer the attached qvw file.

Thanks,

Pooja

[#9]

Hi,

I've attached a new example, please check the last example qvw I uploaded.

The problem is when I'm doing left join,

and unique_nums that don't have "Lead category" are not displayed in the result,

because I have the condition:

Lead_Category-={'My Leads','Renew'}

[sunny_talwar]

May be try this

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

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

Where New_Lead_Category is calculated in the script like this

aa:

LOAD * INLINE [

    Date, Unique_Num, ActionType_Name, Source_Name,SaleAmount

    2017-07-02, 1221709, Lead, Online,0

    2017-07-02, 1685262, Lead, Online,0

    2017-07-02, 2238202, Lead, Online,0

    2017-07-02, 2306472, Lead, Online,0

    2017-07-02, 2440744, Lead, Online,0

    2017-07-02, 2801267, Lead, Online,0

    2017-07-03, 2440744, Sale, Online,20

    2017-07-03, 2801267, Sale, Online,50

    2017-07-01,12345,Lead,Online,0

    2017-07-01,12345,Sale,Email,18

];

left join (aa)

bb:

LOAD * INLINE [

     Unique_Num,  Lead_Category

     1685262,  Renew

     2238202, Canada Start

     2306472, Canada Start

    2440744, My Leads

];

FinalTable:

LOAD *,

If(Len(Trim(Lead_Category)) = 0, 'No Data', Lead_Category) as New_Lead_Category

Resident aa;

DROP Table aa;