Total over previous years

[g_f_bakker]

Hello,

I have a question and I really need some help.

When I use a table in a bar chart with the columns YEAR, PERIOD and QTY, I want to do the following with one expression:

If

YEAR < Max(YEAR)

then (Total QTY of that Year)

Else Sum(QTY)

Only the bar of Max(YEAR) must change when I put a selection on PERIOD.

My test file is attached.

Please help me, I think it’s something with the aggr function, but .....

[deepakk · Accepted solution]

HI,

Try  out the attach application

Deepak

[g_f_bakker]

Hi Deepak,

Absolutely BRILLIANT!!

Could you please explain this syntax? Sum({<YEAR={"< $(vMaxYear)"},PERIOD=>}QTY)

Many thanks, Gerard.

[deepakk]

Hi Gerard,

I am storing the max(year) inside variable vMaxyear. And using set analysis iam checking if Year < vMaxYear

and ignore the selection for period (PERIOD=) then sum of quantity.

Deepak

[g_f_bakker]

Hi Deepak,

Again, many thanks!

Gerard.