Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis

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 2nd, and bought on July 3rd 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)


1 Solution

Accepted Solutions
pooja_prabhu_n
Creator III
Creator III

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)

View solution in original post

11 Replies
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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

It not working....

balar025
Creator III
Creator III

Hi,

I have made changes in script and your expression.

Please find the attachment.

vinieme12
Champion II
Champion II

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
Champion II
Champion II

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)

Not applicable
Author

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
Creator III
Creator III

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

Not applicable
Author

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;