Average 30 last days for each date selected

[#1]

Hello Everyone,

I will give you an example if you can help me to resolve my problem. i was not able to resolve the problem since one week

I have to compare the number of transactions of one day to last 3 days.

for the date i have a field : Date_Appli

22/09/2015

23/09/2015

24/09/2015

25/09/2015

The field dimension is   "primary_asset_class" which has 5 values

PRIMARY_ASSET_CLASS	= sum(FLAG_DTCC_EOD_SENT)	Average
	5469306	273,034
Equity	1600973	400,386
InterestRate	1583450	394,698
ForeignExchange	1156549	288,403
Credit	733281	183,049
Commodity	395053	98,635

to calculate the average  i have used this expression :

  =Avg(aggr(sum({<DATE_APPLI={">=$(MinDate) < $(MaxDate)"}>} FLAG_DTCC_EOD_SENT),PRIMARY_ASSET_CLASS,DATE_APPLI))

For information i have defined the variables used in expression like this :

MaxDate  =Floor(max(DATE_APPLI))

MinDate = MaxDate -3

Normaly when i select 25/09/2015 (Sept 25th 2015) i will get in the the ceil "average" the average of the transaction of 3 previous days (Sept 24th, sept 23th and sept 22th). but it not working --> it shows this :

PRIMARY_ASSET_CLASS	= sum(FLAG_DTCC_EOD_SENT)	Average
	1373791	-
Equity	399815	-
InterestRate	399355	-
ForeignExchange	291340	-
Credit	184133	-
Commodity	99148	-

Thank you very much in advance for your help

Regards,

[JonasValleskog]

Hi Gabriel,

Can you verify if this expression isn't per chance giving the same results?

I see no reason why your outer aggregation function

Avg({1} .... )

should not actually be written as:

Avg({1<[Application date]={">=$(vMinDate)<$(vMaxDate)"}, DealValo={1}>} ....)

The inner scope inside your count is restricted, so reasonably the outer scope of your average should follow suit. You'll likely get the same results from both, I'm simply trying to promote a principal of syntax implementation that works in all scenarios - i.e. something that could be called "Best Practice". My version will likely perform marginally better, as the outer aggregation scope is now unnecessarily wide.

Regards

Jonas