Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis A - B not working properly

Hello Community,

I'm struggling for a few days now without success using Set Analysis for a complicated goal.

I have a Sales table, with customers, products, year and product family, among other unecessary fields.

I want to show a concat list of product families that each customer bought in 2014 but not in 2015.

My progress so far is:

Concat( {<Year = {2014}> - <Year = {2015}>} DISTINCT ProductFamily, ', ')

But this doesn't seem to work. My guess is that it's not excluding the ProductFamily from first set, but the whole row, which obviously doesn't exist.

This means that my result is:

If customer bought x, y, z in 2014 and nothing in 2015, it shows x, y, z (always shows whole 2014 as the result)

If customer bought x, y, z in 2014 and x in 2015, it shows nothing (always empty if customer bought anything in 2014 AND 2015)

I have been trying to achieve something like this:

<{ ProductFamily = { <Year = {2014}> ProductFamily - <Year = {2015}> ProductFamily } }>

But, unsurprisingly (bad syntax heh) it doesn't work.

Any clues?

Thanks!

1 Solution

Accepted Solutions
cesaraccardi
Specialist
Specialist

Hi,

That's a tricky one. I would suggest that you create an extra dummy field on your loading script:

Customer & '|' & ProductFamily as Dummy

And then you write your expression like this:

=Concat({$<Year={2014},Dummy=E({$<Year={2015}>} Dummy)>} DISTINCT ProductFamily, ',')

I appreciate is not an elegant solution but seems to work fine.

Cesar

View solution in original post

10 Replies
Not applicable
Author

try following code,it will exclude 2015 from your calculation set

Concat( {<Year =- {2015}> } DISTINCT ProductFamily, ', ')

Not applicable
Author

It's not exactly what I'm looking for. It will show a product family of 2014 despite it has been purchased in 2015. I need to remove from 2014 every product family ever bought in 2015.

Anyway, thanks for the interaction!

Not applicable
Author

Hi,

why can't you try P() and E() ?

jerrysvensson
Partner - Specialist II
Partner - Specialist II

Have a look at P() and E() functions, set analysis. It might help you.

Not applicable
Author

I have tried P() and it works only if I select a single customer.

I suppose this happens because many other product families (from other customers) are possible, breaking the set I wanted.

I can't imagine a way to work with E(). Any ideas?

kuba_michalik
Partner - Specialist
Partner - Specialist

=Concat({<ProductFamily=P({<Year={2014}>}ProductFamily)*E({<Year={2015}>}ProductFamily)>}DISTINCT ProductFamily,',')

This will give you an intersection of products which were Possible for 2014 and at the same time Excluded for 2015. Coupled with Customer as dimension, it should give you what you need.

Edit: damn, it won't work, set analysis always evaluates before any chart dimensions.

cesaraccardi
Specialist
Specialist

Hi,

That's a tricky one. I would suggest that you create an extra dummy field on your loading script:

Customer & '|' & ProductFamily as Dummy

And then you write your expression like this:

=Concat({$<Year={2014},Dummy=E({$<Year={2015}>} Dummy)>} DISTINCT ProductFamily, ',')

I appreciate is not an elegant solution but seems to work fine.

Cesar

Not applicable
Author

Your edit seems right

It got me to the same result as before, but with a higher complexity

It was a smart aproach, tho. Thanks for trying!

Not applicable
Author

This requires something more than tweaking formulas, but it's something I will try. In fact, I got my hopes up

Reporting back soon...