Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
guillaume_gorli
Creator II
Creator II

Intersection using set analysis

Hi All,

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

Capture.JPG

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 :

Capture.JPG

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

Capture.JPG

Attached are database and .qvw

Any help ?

Thanks in advance

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Use below expression for 2014

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

View solution in original post

9 Replies
awhitfield
Partner - Champion
Partner - Champion

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
MVP
MVP

Use below expression for 2014

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

anbu1984
Master III
Master III

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
Former Employee
Former Employee

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

Image1.png

Anonymous
Not applicable

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
Creator II
Creator II
Author

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

guillaume_gorli
Creator II
Creator II
Author

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

guillaume_gorli
Creator II
Creator II
Author

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

guillaume_gorli
Creator II
Creator II
Author

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