YTD Average per month using Avg/Aggr

[kenphamvn]

Dear All, stalwar1‌

i want to calculate AVG from first month of Year to selected month like formula

Avg_YTD= (avg for each month/number of month)

example:

Month	Values
Jan-2018	2
Jan-2018	4
Feb-2018	5
Feb-2018	4
Mar-2018	3
Apr-2018	3
Apr-2018	2

if i selected Mar-2018,it will be calculate from Jan-2018 to Mar-2018  (3 months)

Expected result is

Avg_YTD = (avg(2+4) + avg(5+4) + avg(3)) /3 = 3.5

but i am not sure how to calculate the % change correctly. Any help would be appreciated.

Thanks in advanced

An Pham

[YoussefBelloum]

Hi,

Try this:

convert your date field on the script like this:

Date(floor(Date#(Month,'MMM-YYYY')),'MM-YYYY') as Month

on your chart, use this expression:

=avg({<Month={"<=$(=Max(Month))>=$(=Min(Month))"}>}Values)

[sunny_talwar]

May be this

Avg({<SetAnalysisOnDateOrMonth>} Aggr(Sum(Values), Month))

[kenphamvn]

Thank stalwar1‌

i do as your instruction but maybe i miss something

Here my code

avg({
<
[TranDate.autoCalendar.Date]={
            ">=$(=YearStart(Max([TranDate.autoCalendar.Date])))<=$(=Max([TranDate.autoCalendar.Date]))"
            }
>}aggr($(vw_%PC),[TranDate.autoCalendar.YearMonth]))

//vw_%PC =sum(PC)/Sum(SC)

And here my result

When Non YearMonth or All YearMonth selected, it's correct

When YearMonth selected, it's calculate base on selected values

Example, if 2018-Mar and 2018-Feb selected, result is AVG =(AVG(2018-Mar) +AVG(2018-Feb))/2

instead expect result is AVG =(AVG(2018-Mar) +AVG(2018-Feb) + AVG(2018-Jan))/3   (YTD)

Any help would be appreciated.

Thanks in advanced

An Pham

[sunny_talwar · Accepted solution]

You will have to use your set analysis here also

Sum({<[TranDate.autoCalendar.Date] = {">=$(=YearStart(Max([TranDate.autoCalendar.Date])))<=$(=Max([TranDate.autoCalendar.Date]))"}>} PC)/Sum({<[TranDate.autoCalendar.Date] = {">=$(=YearStart(Max([TranDate.autoCalendar.Date])))<=$(=Max([TranDate.autoCalendar.Date]))"}>}SC)

[kenphamvn]

Thanks Sunny, it's work