Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kenphamvn
Creator III
Creator III

YTD Average per month using Avg/Aggr

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:

MonthValues
Jan-20182
Jan-20184
Feb-20185
Feb-20184
Mar-20183
Apr-20183
Apr-20182

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

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

5 Replies
YoussefBelloum
Champion
Champion

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
Creator III
Creator III
Author

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

4-16-2018 4-58-49 PM.png

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

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
Creator III
Creator III
Author

Thanks Sunny, it's work