Sales of Year and Year-1

[slaheddinekeyru]

Hello,

I'll be very gratefull if you ca help me to get this result :

For each Entity I want to get the value of the year. The expression to use will be (sum(Sales)). But how can I get the Sales for N-1 by Entity ?

Year	Entity	N	N-1
1996	A	354	-
1996	B	314	-
1996	C	429	-
1996	E	280	-
1996	F	485	-
1997	A	354	354
1997	B	318	314
1997	C	437	429
1997	D	494	-
1997	F	492	485
1998	A	374	354
1998	B	319	318
1998	C	448	437
1998	D	491	494
1998	F	520	492
1999	A	359	374
1999	B	321	319
1999	C	464	448
1999	D	495	491
1999	E	309	-
1999	F	521	520

Thank you very much for all your answers

[sergio0592]

Just try with : above(TOTAL sum(N),6)

[slaheddinekeyru]

Thank You Jean-Baptiste,

The problem that your solution supposes that we have the same number of entities in each year. But in reality it's not.

That's why I avoid using the Above function.

[sunny_talwar]

Use The As-Of Table

[sunny_talwar]

Or try this

Aggr(Above(Sum({<Year>}Sales)), Entity, Year)

[slaheddinekeyru]

Thank You Sunny for your answer,

If we can just sort fields into the aggr it will be ok. But the result I get depends on the loading sort. I tried to sort data by Entity, Year asc but without success.

[sunny_talwar · Accepted solution]

You can sort in Aggr() if you are using QV 12 or above or Qlik Sense, using this expression

Aggr(Above(Sum({<Year>}Sales)), Entity, (Year, (NUMERIC)))

[slaheddinekeyru]

Thank you very much Sunny !

You are simply incredible