Can't get Set Analysis "Intersect" to work!

[Jason_Michaelides]

Hi all,

I have tried in vain to use the help files for this - I'm sure it should be straight forward, but I'm just getting used to Set Analysis...

I have a Tx table with:

• Tx_ID
• Customer_ID
• ShowYear

The same customer can have multiple transactions for each Show Year.

When I select a show year in a list box I want to see the number of distinct customers that bought tickets that year. Simple enough

COUNT(DISTINCT Customer_ID)

However, I now want to see how many of these customers also bought tickets for any previous year. I've tried many different ways, including (to test) creating a bookmark of all previous years and then:

COUNT( {$*IntersectTest} DISTINCT Customer_ID)

to try and replicate the F1 help example of

sum( {$*BM01} Sales )

but nothing has worked!

Please help if you can!

Thanks,

Jason

[#2 · Accepted solution]

HI,

Here you are a possible solution.

I hope it helps you.

Regards

[Jason_Michaelides]

Hi Miguel,

Thank you! I did actually try this way before and had:

=count({$<Customer_ID = P({1<ShowYear={'<$(#v_year)'}>} Customer_ID) >} Distinct Customer_ID)

The problem I had was I was having to define every field selection to get the right number and didn't know why. Looking at your solution it was a simple 1 instead of $ - oops!

Thanks again mate.

Jason

[Jason_Michaelides]

Another question for you Miguel!

I also have a [Transaction Type] field and want to limit the Distinct Count of Customer_ID to those with a particular Tx type only. I've tried:

COUNT( {$<Customer_ID= P({$<[Show Year]={'<$(#vShowYear)'},[Transaction Type]={'TICKET SALE'} >}Customer_ID)>} DISTINCT Customer_ID)

but this isn't correct. It should give the same result as manually selecting a [Transaction Type] in the list box.

Thanks,

Jason

[#5]

HI Jason,

Based on my example, I've made the selection using Tx_ID and it works for me, try with that and you would see where is the mistake.

count({$<Customer_ID = P({$<ShowYear={'<$(#v_year)'}>} Customer_ID), Tx_ID = {'1'} >} Distinct Customer_ID)

Regards.

[Jason_Michaelides]

Hi Miguel,

Sorry for the delayed reply - been drawn onto other things!

I've tried this and can't get it to work. I wonder if it's because the Tx Type field is in another table? Here's my table layout:

It's not very clear but you should be able to make out the fields Individual_ID_Master (Individuals table), [Show Year] (Shows table)and [Transaction Type] (Transactions table). My expression for a distinct count on Individuals is

COUNT({$<Individual_ID_Master = P({$<[Show Year]={'<$(#vShowYear)'}>} Individual_ID_Master), [Transaction Type] = {'TICKET SALE'} >} DISTINCT Individual_ID_Master)

This expression gives a result of 4,345. If I now select 'TICKET SALE' from a Transaction Type list box the number drops to 3,783. If the set analysis expression was working correctly there should be no change in the value, correct?

I'm sure I'm missing something obvious here!

Thanks,

Jason

[#7]

Hi Jason,

I'm sorry, I've not been able to answer you before, I've created a few data example for checking your doubt, but the result is the expected. I've attached my test file. If you could be more especific about your data maybe I would be helpfull.

Regards.

[Jason_Michaelides]

Hi Miguel - Happy New Year to you!

With a little help from a QlikView consultant I got this working. I needed the set modifier in both the main set and the implicit set:

COUNT( {$<Individual_ID_Master = P({$<[Show Year]={'<$(#vShowYear)'},[Transaction Type]={'TICKET SALE'} >}Individual_ID_Master ),[Transaction Type]={'TICKET SALE'} >} DISTINCT Individual_ID_Master )

This worked OK!

Thanks again for your help on this,

Jason