topic Set Analysis A - B not working properly in QlikView

Set Analysis A - B not working properly

Thu, 22 Jan 2015 11:41:59 GMT

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!

Re: Set Analysis A - B not working properly

Thu, 22 Jan 2015 11:55:45 GMT

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

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

Re: Set Analysis A - B not working properly

Thu, 22 Jan 2015 11:59:50 GMT

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!

Re: Set Analysis A - B not working properly

Thu, 22 Jan 2015 12:05:09 GMT

Hi,

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

Re: Set Analysis A - B not working properly

jerrysvensson — Thu, 22 Jan 2015 12:05:13 GMT

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

Re: Set Analysis A - B not working properly

Thu, 22 Jan 2015 12:07:49 GMT

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?

Re: Set Analysis A - B not working properly

kuba_michalik — Thu, 22 Jan 2015 12:23:31 GMT

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

Re: Set Analysis A - B not working properly

cesaraccardi — Thu, 22 Jan 2015 13:12:14 GMT

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

Re: Set Analysis A - B not working properly

Thu, 22 Jan 2015 17:10:24 GMT

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!

Re: Set Analysis A - B not working properly

Thu, 22 Jan 2015 17:12:12 GMT

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

Reporting back soon...

Re: Set Analysis A - B not working properly

Thu, 22 Jan 2015 17:25:45 GMT

Absolutely right!

Ladies and Gentleman, we have a new champion!

Thank you!!!