topic Re: Intersection using set analysis in QlikView

Intersection using set analysis

guillaume_gorli — Fri, 27 Mar 2015 11:23:36 GMT

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

Re: Intersection using set analysis

awhitfield — Fri, 27 Mar 2015 11:36:47 GMT

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?

Re: Intersection using set analysis

MK_QSL — Fri, 27 Mar 2015 11:37:14 GMT

Use below expression for 2014

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

Re: Intersection using set analysis

anbu1984 — Fri, 27 Mar 2015 11:43:25 GMT

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)

Re: Intersection using set analysis

hic — Fri, 27 Mar 2015 11:44:20 GMT

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

Re: Intersection using set analysis

Anonymous — Fri, 27 Mar 2015 11:49:53 GMT

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

Re: Intersection using set analysis

guillaume_gorli — Fri, 27 Mar 2015 13:51:10 GMT

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

Re: Intersection using set analysis

guillaume_gorli — Mon, 30 Mar 2015 13:10:49 GMT

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

Re: Intersection using set analysis

guillaume_gorli — Mon, 30 Mar 2015 13:12:09 GMT

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

Re: Intersection using set analysis

guillaume_gorli — Mon, 30 Mar 2015 14:45:39 GMT

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