Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

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

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

1 Solution

Accepted Solutions

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

7 Replies
paulyeo11
Valued Contributor II

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

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

paulyeo11
Valued Contributor II

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

Hi Sunny

Thank you very much your expression work fine.

Paul

paulyeo11
Valued Contributor II

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.

Hope you can advise me.

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

paulyeo11
Valued Contributor II

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 .

Hope you can advise me.

Paul

Sent from my iPhone

Community Browser