Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
May be this
Avg({<SetAnalysisOnDateOrMonth>} Aggr(Sum(Values), Month))
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
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)
Thanks Sunny, it's work