7 Replies Latest reply: Aug 4, 2016 8:43 PM by Yeo Poh sai

# SET expression give YTD value , How to make it Average amount ?

Hi All

Below expression from Ruben , work fine  , which give the current select date aR amount :-

I need to modify the above expression to get the average value of aR , to make it display -5,234,734

From Table 1

YearMonth "Jun-2016

(S\$K)"

June -4,619,754

May -5,286,254

Apr -5,702,825

Mar -5,627,692

Feb -5,256,865

Jan -4,915,070

Total -31,408,460

no of mth 6

Average -5,234,743

i Try add AVG at the expression , it does not work :-

AVG(

)

Hope some one can advise me.

Paul

my qvw

• ###### Re: SET expression give YTD value , How to make it Average amount ?

This works, but I am not sure if this is something you would want to dot because when July number comes, you will have to modify your expression:

RangeAvg(

Sum({\$<year, month, date = {'<=\$(=AddMonths(Max(date), -0))'}>}TOTAL Aggr(Sum({<year, month, date={'<=\$(=AddMonths(Max(date), -0))'},aR = {'aR'}>}[Amount]), YearMonth)),

Sum({\$<year, month, date = {'<=\$(=AddMonths(Max(date), -1))'}>}TOTAL Aggr(Sum({<year, month, date={'<=\$(=AddMonths(Max(date), -1))'},aR = {'aR'}>}[Amount]), YearMonth)),

Sum({\$<year, month, date = {'<=\$(=AddMonths(Max(date), -2))'}>}TOTAL Aggr(Sum({<year, month, date={'<=\$(=AddMonths(Max(date), -2))'},aR = {'aR'}>}[Amount]), YearMonth)),

Sum({\$<year, month, date = {'<=\$(=AddMonths(Max(date), -3))'}>}TOTAL Aggr(Sum({<year, month, date={'<=\$(=AddMonths(Max(date), -3))'},aR = {'aR'}>}[Amount]), YearMonth)),

Sum({\$<year, month, date = {'<=\$(=AddMonths(Max(date), -4))'}>}TOTAL Aggr(Sum({<year, month, date={'<=\$(=AddMonths(Max(date), -4))'},aR = {'aR'}>}[Amount]), YearMonth)),

Sum({\$<year, month, date = {'<=\$(=AddMonths(Max(date), -5))'}>}TOTAL Aggr(Sum({<year, month, date={'<=\$(=AddMonths(Max(date), -5))'},aR = {'aR'}>}[Amount]), YearMonth)))

• ###### Re: SET expression give YTD value , How to make it Average amount ?

You can also try this which will work in Qlik Sense , but not in QV9

=Aggr(If(YearMonth = Max(TOTAL YearMonth),

RangeAvg(Below(RangeSum(

Above(RangeSum(Above(Sum({<year, month>}[Amount]*-1), 0, RowNo()))),

-Sum(TOTAL Aggr(Sum({<year, month>}[Amount]*-1), YearMonth))), 0, Max(TOTAL {<year = {\$(=Max(year))}>} month)))), (YearMonth, (NUMERIC, desc)))

• ###### Re: SET expression give YTD value , How to make it Average amount ?

Hi Sunny

Thank you very much your expression work fine.

Paul

• ###### Re: SET expression give YTD value , How to make it Average amount ?

Hi Sunny

I carefully look at your expression which work in QS , it there a way convert it to SET expression and allow user to select year and month ? As I need to cal the CY LY PY value and make compare , in order to compute the cash conversion cycle for aR aP and sTOCK.

Paul

Sent from my iPhone

• ###### Re: SET expression give YTD value , How to make it Average amount ?

I will look at this tomorrow, going off to sleep now

• ###### Re: SET expression give YTD value , How to make it Average amount ?

Hi Sunny

I want to try using your propose SET expression for getting average amount for current year .

May I know if I need to get LY average amount , it there a way that I can using the same expression but change to month_n or using if LY_FLAG , and YTD_FLAG , so that I don't need to modify the expression .