Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)))
my qvw
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)))
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)))
Hi Sunny
Thank you very much your expression work fine.
Paul
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
I will look at this tomorrow, going off to sleep now
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