Accumulated average and set expression

[Anonymous]

Hi, folks.

I need to implement a kind of "accumulated"/moving average. I am stuck with the set expression to determine the calculation universe I will use.

I have some ticket data as the example below:

RecNo	Snapshot_Date	Ticket	Ticket_Age
1	06/10/2018	A	10
2	06/10/2018	B	5
3	6/10/2016	C	20
4	06/11/2018	A	10
5	06/11/2018	B	6
6	06/11/2018	C	21
7	06/12/2018	A	10
8	06/12/2018	B	7
9	06/12/2018	C	21
10	06/12/2018	D	1

The user must be able to select whatever Snapshot_Date she wants. Qlik should calculate the "accumulated average age" up to the selected Snapshot_Date. Some examples:

User Snapshot_Date selection: 06/10/2018

Result: 11.67

Calculation: (10 + 5 + 20) / 3

Corresponding RecNo: 1 to 3

User Snapshot_Date selection: 06/11/2018

Result: 12

Calculation: (10 + 5 + 20 + 10 + 6 + 21) / 6

Corresponding RecNo: 1 to 6

User Snapshot_Date selection: 06/11/2018 and 06/12/2018

Result: 10.86

Calculation: (10 + 6 + 21 + 10 + 7 + 21 + 1) / 7

Corresponding RecNo: 4 to 10

I try using something like the calculation below, to no avail.

Sum({$ <Snapshot_Date = {"<=$(=Max([Snapshot_Date]))"}>} Ticket_Age) / Count({$<snapshot_date = {"<=$(=Max(Snapshot_Date))"}>} Ticket)

Any advice?

[sibusiso90]

Hi Al,

Please find the QVW below I used range sum hope this helps, not quite a complete solution as it doesn't select maybe there is something you are picking up that I am not.

Kind Regards

Sibusiso Phumelo !

[Anonymous]

Unfortunately, I am not able to import apps in my environment. Could you please describe what was your solution instead?

Thank you for your help.

[sibusiso90 · Accepted solution]

Hi Al,

I used the following expression.

Rangesum(sum({$<Date={"<=$(max(num(Date)))"}>} Value),before(sum({<Date={"<=$(max(num(Date)))"}>} Value),1,ColumnNo()))

/Rangesum(Count({$<Date={"<=$(max(num(Date)))"}>} Value),before(count({<Date={"<=$(max(num(Date)))"}>} Value),1,ColumnNo()))

The only trick is that you have to use it in a pivot that is why I was saying maybe you might find another way to solve the problem.

[Anonymous]

Thanks. It works like a charm!

[DevCon]

Hi all,

Another sollution is the following:

avg( {$<Date={">=$(=date#( max( min(Date), min(total Date) ), 'MM/DD/YYYY' )) <=$(=date#( max(Date), 'MM/DD/YYYY' ))"}>} Value )

I used sibusi's qvw. Thanks sibusi!