topic Re: Set Analysis in QlikView

Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: Set Analysis

Anil_Babu_Samineni — Tue, 04 Jul 2017 11:24:54 GMT

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

Re: Set Analysis

Tue, 04 Jul 2017 11:46:11 GMT

It not working....

Re: Set Analysis

balar025 — Tue, 04 Jul 2017 12:27:57 GMT

Hi,

I have made changes in script and your expression.

Please find the attachment.

Re: Set Analysis

vinieme12 — Tue, 04 Jul 2017 13:45:57 GMT

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)

Re: Set Analysis

vinieme12 — Tue, 04 Jul 2017 13:50:25 GMT

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)

Re: Set Analysis

Wed, 05 Jul 2017 09:55:44 GMT

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

Re: Set Analysis

pooja_prabhu_n — Wed, 05 Jul 2017 10:25:04 GMT

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

Re: Set Analysis

Wed, 05 Jul 2017 10:52:02 GMT

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'}

Re: Set Analysis

sunny_talwar — Wed, 05 Jul 2017 11:20:12 GMT

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;

Re: Set Analysis

pooja_prabhu_n — Wed, 05 Jul 2017 11:59:11 GMT

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)

Re: Set Analysis

Wed, 05 Jul 2017 12:14:39 GMT

Perfect!