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

[paulyeo11]

Hi All

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

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

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(

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

)

Hope some one can advise me.

Paul

[paulyeo11]

my qvw

[sunny_talwar]

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)))

[sunny_talwar · Accepted solution]

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)))

[paulyeo11]

Hi Sunny

Thank you very much your expression work fine.

Paul

[paulyeo11]

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.

Hope you can advise me.

Paul

Sent from my iPhone

[sunny_talwar]

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

[paulyeo11]

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 .

Hope you can advise me.

Paul

Sent from my iPhone