Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Use the below expresson
Sum({<Year={$(=max(Year))>} FieldName)
This will select only one year.
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?
dont use only function ...... use Max function.. it will work
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
Can you attach the application... We can check it out..
I have implemeted it many times.