Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
try following code,it will exclude 2015 from your calculation set
Concat( {<Year =- {2015}> } DISTINCT ProductFamily, ', ')
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!
Hi,
why can't you try P() and E() ?
Have a look at P() and E() functions, set analysis. It might help you.
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?
=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.
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
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!
This requires something more than tweaking formulas, but it's something I will try. In fact, I got my hopes up
Reporting back soon...