Inter-record calculation

[emmanueld]

Hello gentlemen, ladies,

I am struggling with some calculations using inter-record functions. I'm not sure exactly how to do, and what functions is best using. Can you help me please? 

You'll find attached the detail of what I'm trying to do. 

First tab : this is the basic calculation I make in order to get one value per month (no problem so far).

Second tab (where I struggle): Now I try to calculate a Year-to-date average for each month, utilising the values of each previous month in the same year.

Third tab (just a filter, so it should be easy): the final result I'm expecting, that is, a graph showing the YTD values of december on previous years, and the YTD values of each month on current year.

Thanks

If no better idea, I will try using (current_value + above(1) + above(2) +... + above(11))/12 but that looks dirty to me 🙂

[emmanueld]

I'm starting to think the best I can do now is use to script to fill some new fields.

In the meantime, here is what I came up with (result attached), but for now it's not satisfying (I only want Exp3 in my graph but it depends on the other ones so I can't remove them).

Dimension = [Year/Month]

Exp1 = MonthlyFigure = count({$} DISTINCT [Id])

Exp2 = YTD_Average = ([MonthlyFigure]
+ if([Year] = Above([Year]),Above([MonthlyFigure]),0) //M-1
+ if([Year]= Above([Year],2),Above([MonthlyFigure],2),0) //M-2
+ if([Year] = Above([Year],3),Above([MonthlyFigure],3),0) //M-3
+ if([Year] = Above([Year],4),Above([MonthlyFigure],4),0) //M-4
+ if([Year] = Above([Year],5),Above([MonthlyFigure],5),0) //M-5
+ if([Year] = Above([Year],6),Above([MonthlyFigure],6),0) //M-6
+ if([Year] = Above([Year],7),Above([MonthlyFigure],7),0) //M-7
+ if([Year] = Above([Year],8),Above([MonthlyFigure],8),0) //M-8
+ if([Year] = Above([Year],9),Above([MonthlyFigure],9),0) //M-9
+ if([Year] = Above([Year],10),Above([MonthlyFigure],10),0) //M-10
+ if([Year] = Above([Year],11),Above([MonthlyFigure,11),0) //M-11
)/[Month]

Exp3 = YearlyAverageAndCurrentYTD = if([Year] = vCurrentYear OR [Month] = 12, [YTD_Average], 0)