Intersection using set analysis

[guillaume_gorli]

Hi All,

i have a simple database attached wich is showing three informations : part number, date and quantity

i would like to show on a table quantity of part numbers consummed in 2015 as well as the ones consummed in 2014.

What i got from my app is below table :

Although what i am looking for should looks like : only part number A should be taken into account as consummed both in 2014 and 2015

Attached are database and .qvw

Any help ?

Thanks in advance

[awhitfield]

Hi Gulilaume,

The trick is that i do want to show only part numbers consummed in 2015 AND 2014.

So should the resulting chart only show A, as that the only one in both 2015 and 2014?

[MK_QSL · Accepted solution]

Use below expression for 2014

SUM({<Année = {2014},[Part number] = P({<Année= {2015}>}[Part number])>}Qty)

[anbu1984]

LOAD [Part number], 
     Date, 
     Year(Date) As Yr,
     Qty
FROM
[TEST exclusion.xlsx]
(ooxml, embedded labels, table is Feuil1);

Dim: [Part number]

Expr: If(Sum({<Yr={2015}>}Qty)=0,0,Sum({<Yr={2014}>}Qty))

Sum({<Yr={2015}>}Qty)

[hic]

To find the part numbers that have been consumed both years, you can use

  = Sum ({$<[Part number]=P({$<Année={2014}>} [Part number])*P({$<Année={2015}>} [Part number])>}Qty)

and to have the same condition, but split per year, you can use

   = Sum ({$<Année={2015},[Part number]=P({$<Année={2014}>} [Part number])*P({$<Année={2015}>} [Part number])>}Qty)

But I don't understand your last table. That C should be excluded, is clear. But so should B and D as I see it.

HIC

[Anonymous]

See attached.

I replaced your dimension with a calculated dimension excluding Part numbers with 0 sales in 2015 (if this is what you need - not quite clear from your description).

[guillaume_gorli]

thanks guys, i will check your feedback by monday and come back to you

[guillaume_gorli]

You are right Andrew. My mistake : the resulting chart should only show A

[guillaume_gorli]

Correct Henric, i wrongly describe the issue : B,C and D should be excluded, not only A

[guillaume_gorli]

Thanks guys , your feebacks helped me to understand i am facing other issuesto build the app requested. I need to describe those more precisely in a new trend as Manish Kachhia answerd the initial question even though the question was not quite clear)

Guillaume