topic Re: Last year values in combined set analysis in QlikView

Last year values in combined set analysis

Hoowling — Sat, 16 Nov 2024 02:13:57 GMT

Hi,

I have a graph that looks like this, where the scrapped value (Skrotvärde (kr)) and the scrapped value per machine hour (Kostnad per timme (kr)) is presented.

The expression for "kostnad per timme (kr)" is:

-sum(Price)/
Sum({1<
YearMonth = $::YearMonth,
Year = $::Year,
ScrapLocation = $::ScrapLocation,
Month = $::Month,
Produktionsgrupp = $::Produktionsgrupp

>}MachineHours)

The negative sign is used since "Price" (which is the scrapped value) is reported with negative values.
I've used a set analysis so that only certain selections will affect the graph.
Now I want to add a line to the graph where the scrappad value per machine hour is presented for the prior year.

Normally I would use an expression like this: sum({$<Year={$(=Max(Year)-1)}> } [Price]).

But how do I incorporate the Max(Year)-1 part into the expression above where I already use the set analysis to exclude certain selections? I've tried to play around with the expression but havn't been able to find the right one.

//Hoowling

Re: Last year values in combined set analysis

Hoowling — Mon, 21 Oct 2019 06:29:09 GMT

No ideas?

Re: Last year values in combined set analysis

jaibau1993 — Mon, 21 Oct 2019 07:16:13 GMT

Hi!

I think that the expression

-sum(Price)/
Sum({1<
YearMonth = $::YearMonth,
Year={$(=Max(Year)-1)},
ScrapLocation = $::ScrapLocation,
Month = $::Month,
Produktionsgrupp = $::Produktionsgrupp

>}MachineHours)

should work EXCEPT for selections in YearMonth because joins (current) Year and Month. The easiest way to solve it is to remove the YearMonth = $::YearMonth part of the set analysis.

Regards,

Jaime.

Re: Last year values in combined set analysis

Hoowling — Mon, 21 Oct 2019 10:00:03 GMT

Worked perfectly - thank you!