Qlik Community

Qlik Sense Cloud Discussions

jonnieyacoub
Contributor

Set Analysis inside Set Analysis

Hey community,

I have some trouble with an Set Analysis expression. I have the following table: [lead_id], [lead_inserted], [lead_name_last], [lead_priority], [lead_type] (this is lead or deal),[lead_source].

I have made an Bar chart with the dimension [lead_inserted] with the possibility to drill down (year, month, date). For the expression i want an to count ALL of the [lead_type] "lead", the [lead_inserted] needs to start from 14/02/2017 (D/M/YYYY) and from the [lead_source] i want to EXCLUDE "ld-landlord". I tried the following expression without the result i would like:

=Count({<[lead_type] ={"lead"}>

+ = $ < [lead_inserted.autoCalendar.Date]={">=14/2/2017"}>

+ = <$-1<[lead_source]={"ld-landlord"}>}

[lead_id-lead_location_lead])

With an single set analysis expression it works. Like this:

=Count({<[lead_type] ={"lead"}>}

[lead_id-lead_location_lead])

Can anybody help me with my expression?

Thankss!

1 Solution

Accepted Solutions

Re: Set Analysis inside Set Analysis

Did you forget to add a minus sign after lead_source

=Count({<[lead_type] ={"lead"}, [lead_inserted.autoCalendar.Date]={">=14/2/2017"},[lead_source] -= {"ld-landlord"}>}

[lead_id-lead_location_lead])

12 Replies
antoniotiman
Honored Contributor III

Re: Set Analysis inside Set Analysis

Try

=Count({<[lead_type] ={"lead"}, [lead_inserted.autoCalendar.Date]={">=14/2/2017"},[lead_source]={"ld-landlord"}>}

[lead_id-lead_location_lead])

Re: Set Analysis inside Set Analysis

Did you forget to add a minus sign after lead_source

=Count({<[lead_type] ={"lead"}, [lead_inserted.autoCalendar.Date]={">=14/2/2017"},[lead_source] -= {"ld-landlord"}>}

[lead_id-lead_location_lead])

jonnieyacoub
Contributor

Re: Set Analysis inside Set Analysis

Antonio & Sunny,

Thank you for the responses! it worked with the -={....}! just one more question.. what if i want to count the lead AND deals.. i tried this: [lead_type] ={"lead","deal"} and this: [lead_type] ={"*"}... without result...


Its because i have a combo bar/line chart and i want hem to count in one measure the leads + deals and in the other measure only the deals (this measure i have)...

Jonnie

Re: Set Analysis inside Set Analysis

Did you try like this?

=Count({<[lead_type] = {"lead", "deal"}, [lead_inserted.autoCalendar.Date] = {">=14/2/2017"}, [lead_source] -= {"ld-landlord"}>} [lead_id-lead_location_lead])

jonnieyacoub
Contributor

Re: Set Analysis inside Set Analysis

Sunny,

yes i tried that... But the bar chart stays the same... the reason i want this is because a deal is also a lead and i would like to visualise the leads vs deals... i have 2 measures now. Measure 1:

=Count({<[lead_type] ={"lead","deal"},

[lead_inserted.autoCalendar.Date]={">=02/14/2017"},

[lead_source] -= {"ld-landlord"}>}

[lead_id-lead_location_lead])

measure 2

=Count({<[lead_type] ={"deal"},

[lead_inserted.autoCalendar.Date]={">=02/14/2017"}>}

[lead_id-lead_location_lead])

Re: Set Analysis inside Set Analysis

the reason i want this is because a deal is also a lead and i would like to visualise the leads vs deals.

Not sure I understand this part... can you show some dummy data to elaborate on this?

jonnieyacoub
Contributor

Re: Set Analysis inside Set Analysis

haha i'll try to explain it more in detail. And i have no dummy data available...

I have a dimension with [lead_inserted] (drill down option year, month, date). And as measures i have one that counts all the leads [lead_type]={"lead"} and the second measure that counts all the deals [lead_type]={"deal"} in the table. But when a lead becomes a deal (and we sell our product) measure 1 does not count the "deal" But i need it to count it because a deal was always a lead in the past! So in measure 1 i need the Set Analysis to count both deal & lead. i tried {"*"} to select the whole column [lead_type] but with no result...

Re: Set Analysis inside Set Analysis

Does the deal rows meet the other conditions?

[lead_inserted.autoCalendar.Date]={">=02/14/2017"},

[lead_source] -= {"ld-landlord"}??

May be what you want is this

=Count({<[lead_type] = {"lead"}, [lead_inserted.autoCalendar.Date] = {">=14/2/2017"}, [lead_source] -= {"ld-landlord"}>+<[lead_type] = {"deal"}, [lead_inserted.autoCalendar.Date] = {">=14/2/2017"}>} [lead_id-lead_location_lead])

or this

=Count({<[lead_type] = {"lead"}, [lead_inserted.autoCalendar.Date] = {">=14/2/2017"}, [lead_source] -= {"ld-landlord"}>+<[lead_type] = {"deal"}>} [lead_id-lead_location_lead])

jonnieyacoub
Contributor

Re: Set Analysis inside Set Analysis

Yessss thats it! totally forgot this option... i thought it was possible in the {"lead","deal"}...

Thank you very much! Much appreciated!!!

Community Browser