Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
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!

Tags (1)
1 Solution

Accepted Solutions
cesaraccardi
Valued Contributor

Re: Set Analysis A - B not working properly

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

10 Replies
Not applicable

Re: Set Analysis A - B not working properly

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

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

Not applicable

Re: Set Analysis A - B not working properly

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

Re: Set Analysis A - B not working properly

Hi,

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

Partner
Partner

Re: Set Analysis A - B not working properly

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

Not applicable

Re: Set Analysis A - B not working properly

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?

Highlighted
Partner
Partner

Re: Set Analysis A - B not working properly

=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
Valued Contributor

Re: Set Analysis A - B not working properly

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

Re: Set Analysis A - B not working properly

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

Re: Set Analysis A - B not working properly

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

Reporting back soon...