Where to include a selection to only one year in this aggr()

[#1]

Dear Ladies and Gentlemen,

I"m looking for way, where to include a limitation to only one year in this aggr():

(sum(aggr(if([Net Value]='0',0,
Sum([Net Value]*CurrencyRate)
-Sum([Direct Material Cost])
-Sum([Material Overhead Cost])
-Sum(if(Type='otherCOGS',([Net Value]*CurrencyRate*Factor),0))
-Sum(if(Type='Total Expenses',([Net Value]*CurrencyRate*Factor),0))
)
,[Main Article Group Description],[Key Market],[Fiscal Month Name],[Country Description],[SSR Sales Order Number]))/Sum([Net Value]*CurrencyRate))

I've tried it with a set analysis after each sum(), but it doesn't worked.

Thank you for your help in advance.

[deepakk]

Use the below expresson

Sum({<Year={$(=max(Year))>} FieldName)

This will select only one year.

[#3]

Dear Deepak Kurup,

this is excatly what I was trying to.

(sum({$<[Fiscal Year]={$(=Only([Fiscal Year]-1))}>}aggr(if([cpa_Net Value]='0',0,
Sum([cpa_Net Value]*CurrencyRate)
-Sum([cpa_Direct Material Cost])
-Sum([cpa_Material Overhead Cost])
-Sum(if(Type='otherCOGS',([cpa_Net Value]*CurrencyRate*Factor),0))
-Sum(if(Type='Total Expenses',([cpa_Net Value]*CurrencyRate*Factor),0))
)
,[cpa_Main Article Group Description],[cpa_Key Market],[cpa_Fiscal Month Name],[cpa_Country Description],[cpa_SSR Sales Order Number]))/Sum([cpa_Net Value]*CurrencyRate))

It does not work, unfortunatelly. Is it the right place where I've put it?

[deepakk]

dont use only function ...... use Max function.. it will work

[#5]

Whether I put it just in the Sum before the aggr()

(sum({$<[cpa_Fiscal Year]={$(=max([cpa_Fiscal Year]))}>}aggr(if([cpa_Net Value]='0',0,
Sum([cpa_Net Value]*CurrencyRate)
-Sum([cpa_Direct Material Cost])
-Sum([cpa_Material Overhead Cost])
-Sum(if(Type='otherCOGS',([cpa_Net Value]*CurrencyRate*Factor),0))
-Sum(if(Type='Total Expenses',([cpa_Net Value]*CurrencyRate*Factor),0))
)
,[cpa_Main Article Group Description],[cpa_Key Market],[cpa_Fiscal Month Name],[cpa_Country Description],[cpa_SSR Sales Order Number]))/Sum([cpa_Net Value]*CurrencyRate))

nore I put it in each of the sum

(sum(aggr(if([cpa_Net Value]='0',0,
Sum({$<[cpa_Fiscal Year]={$(=max([cpa_Fiscal Year]-1))}>}[cpa_Net Value]*CurrencyRate)
-Sum({$<[cpa_Fiscal Year]={$(=max([cpa_Fiscal Year]-1))}>}[cpa_Direct Material Cost])
-Sum({$<[cpa_Fiscal Year]={$(=max([cpa_Fiscal Year]-1))}>}[cpa_Material Overhead Cost])
-Sum({$<[cpa_Fiscal Year]={$(=max([cpa_Fiscal Year]-1))}>}if(Type='otherCOGS',([cpa_Net Value]*CurrencyRate*Factor),0))
-Sum({$<[cpa_Fiscal Year]={$(=max([cpa_Fiscal Year]-1))}>}if(Type='Total Expenses',([cpa_Net Value]*CurrencyRate*Factor),0))
)
,[cpa_Main Article Group Description],[cpa_Key Market],[cpa_Fiscal Month Name],[cpa_Country Description],[cpa_SSR Sales Order Number]))/Sum({$<[cpa_Fiscal Year]={$(=max([cpa_Fiscal Year]-1))}>}[cpa_Net Value]*CurrencyRate))

it does not work

[deepakk]

Can you attach the application... We can check it out..

I have implemeted it many times.