Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate average value

Hi,

I have two tables:

Orders:
load * Inline
[
MemberId,EnterDate
1,'2010/01/01'
2,'2010/01/02'
3,'2010/01/03'

];

Transaction:
load * Inline
[
MemberId,ChargeDate,Amount
1,'2010/01/01',100
2,'2010/01/02',200

1,'2010/02/01',300

];

I want to calculate the average Amount by EnterDate, it looks like this:

Month, Total Members, Average Amount

1, 3, 300/3

You could find there is no transaction for memberid 3, but it still need to be calculated.

How to do it? I want to to show the data in the pivot table.

Thanks.

1 Reply
matt_crowther
Specialist
Specialist

Isaac,

Attached should be a solution. I've created the required values for the calculation in the load script instead of the frontend as the EnterDate and ChargeDate cause a conflict when trying to use one or the other as a base for any table.

Once the data is in via the script then it should be straight forward to use the values as required in a Pivot, chart or table.

Hope that helps,

Matt - Visual Analytics Ltd