Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Accumulated average and set expression

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:

RecNoSnapshot_DateTicketTicket_Age
106/10/2018A10
206/10/2018B5
36/10/2016C20
406/11/2018A10
506/11/2018B6
606/11/2018C21
706/12/2018A10
806/12/2018B7
906/12/2018C21
1006/12/2018D1

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?

1 Solution

Accepted Solutions
sibusiso90
Creator III
Creator III

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.

Accumulated Average.JPG

View solution in original post

5 Replies
sibusiso90
Creator III
Creator III

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
Not applicable
Author

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
Creator III
Creator III

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.

Accumulated Average.JPG

Anonymous
Not applicable
Author

Thanks. It works like a charm!

DevCon
Partner - Contributor
Partner - Contributor

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!