YJ_SG
Contributor III
2020-09-16
04:22 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Average of distinct item
I have a table as below.
Wana to do a average of Unit Price of each item. For example, WK001 item A is 8, B is 10 and C is 8.
Average of unit price should be (8+10+8)/3 = 8.67.
Table:
Load * inline[
Week,Item,Qty,Location,Unit Price
WK001,A,100,S1,8
WK001,A,150,S2,8
WK001,A,200,S3,8
WK001,B,300,S1,10
WK001,B,350,S2,10
WK001,C,400,S2,8
WK001,C,450,S3,8
WK002,A,50,S1,9
WK002,A,80,S2,9
WK002,B,100,S1,12
WK002,B,300,S2,12
WK002,B,500,S3,12
WK002,C,500,S2,10
WK002,C,600,S3,10
];
1,216 Views
1 Solution
Accepted Solutions
YJ_SG
Contributor III
2020-09-18
03:56 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
1,165 Views
2 Replies
Taoufiq_Zarra
MVP
2020-09-16
06:07 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@YJ_SG in script for example
Table:
Load * inline [
Week,Item,Qty,Location,Unit Price
WK001,A,100,S1,8
WK001,A,150,S2,8
WK001,A,200,S3,8
WK001,B,300,S1,10
WK001,B,350,S2,10
WK001,C,400,S2,8
WK001,C,450,S3,8
WK002,A,50,S1,9
WK002,A,80,S2,9
WK002,B,100,S1,12
WK002,B,300,S2,12
WK002,B,500,S3,12
WK002,C,500,S2,10
WK002,C,600,S3,10
];
left join
load Week,Item, avg([Unit Price]) as AverageItem resident Table group by Week,Item;
output:
Regards,
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
1,189 Views
YJ_SG
Contributor III
2020-09-18
03:56 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Avg(Aggr([Unit Price],Item,Week))
It works for me.
1,166 Views