issue with Set Analysis AND

[guillaume_gorli]

Dear Qlik community,

What i am trying to get from a purchasing database is the purchasing price variance per vendor.

Let me explain.

i have a database with several informations :

Vendor (only one vendor in the test database)

Date

Part number

Qty purchased

Unit price

Value

The database is covering 2014 and 2015 figures.

What i am trying to do is gather in a chart all the part numbers consummed in 2014 AND 2015.

Then i need to determine and add the average price per year for each part number as well as the quantity purchased in the selected year.

next step is to determine a purchasing value based on year Y-1 average price (ie 2014) as well as a purchasing value based on year Y average price (ie 2015)

The variance between those two last expressions being the purchasing price variance between 2014 and 2015.

i am not that far from what i am trying to do although there are still issues to nail down.

First is that the chart is showing all the part numbers purchased in 2014 and 2015 although for the ones only purchased in 2014 or 2015 (see below screenshot), the quantity is at zero. I may missed something in the expression :

Thus although the chart should show 70 entries, it is in fact showing 472 lines.

Second issues which mais be the result of the first one is that bottom line, the variance in % is not accurate i don't know why.

Variance between 24 131,69 and 26 077,62 should be 8,06 % (not 25,41%, i don't even know where this figure is coming from actually)

So basically, the purchasing price variance is correct for each part number but the whole figure for the selected vendor is wrong.

Hope all this is clear enough.

For the one who want to help, i attach excel database as well as . qvw

[guillaume_gorli]

Any idea ?

[rubenmarin]

Hi Guillaume, if you want to show only part numbers with sales in 2014 and 2015 you can use this calculated dimension:

=Aggr(If(Sum({<Année= {2014}>} Qte) and Sum({<Année= {2015}>} Qte),[Part number]),[Part number])

Also use something similar to filter values for the total issue, it happens because it's calculationg using all part numbers, not only those with sales in 2104 and 2015

[guillaume_gorli]

Thanks Ruben, the calculated dimension is indeed reducing data to 2014 and 2015 sales.

Meanwhile there is still one last entry showing zero quantity.

i got the logic but i can't figure out how to tackle the total issue.

Same calculated dimension doesn not seem to hacve any impact on the final figure.

[rubenmarin]

Hi Guillaume, to remove that row you only need to check 'Supress when value is null' on dimension

[guillaume_gorli]

Indeed ! thanks a lot.

So that's leave me with the total issue.

I am getting close.

[rubenmarin · Accepted solution]

Maybe is a better way but this expression can work:

=(Sum(Aggr(If(Sum({<Année= {2014}>} Qte) and Sum({<Année= {2015}>} Qte), Sum({<Année = {2015}>} Qte)* Avg({<Année = {2015}>}[Prix Unit])),[Part number]))

  -Sum(Aggr(If(Sum({<Année= {2014}>} Qte) and Sum({<Année= {2015}>} Qte), Sum({<Année = {2015}>}Qte)* Avg({<Année = {2014}>}[Prix Unit])),[Part number])))

/Sum(Aggr(If(Sum({<Année= {2014}>} Qte) and Sum({<Année= {2015}>} Qte), Sum({<Année = {2015}>}Qte)* Avg({<Année = {2014}>}[Prix Unit])),[Part number]))

[guillaume_gorli]

Thanks a lot Ruben, this is perfect