Case 935 How to Get Average amount = 12/2=6 ? Now Getting 3

[paulyeo11]

Hi All

Below is the load script :-

Accounts_001:
LOAD * INLINE [
DocNum,TotalSumSy,Customer_Name
1,3,SERVO ENGINEERING SDN BHD
1,3,SERVO ENGINEERING SDN BHD
2,5,SERVO DYNAMICS SDN BHD
];

For Each i in 501
WhatIfDimension$(i):
LOAD * INLINE [
Pls select $(i)st Dim,Column$(i)
SOURCE,SOURCE
SQ_Num,DocNum
Total,Total
Customer_Name,Customer_Name
];
NEXT i;

My Table Dimension :-

=$(ColumnDim501)

My Table expression :-

=pick(Match([Pls select 61st Dim],
'Ave_SQ_Amt',
'Total_SQ',
'Total_Amount',
),
sum($(ColumnDim61))/count(DISTINCT $(ColumnDim61)),
count(DISTINCT $(ColumnDim61)),
sum($(ColumnDim61))
)

Total Amount = 12 is correct :-

Total number of Quotation = 2 is correct :-

Base on above Total =12 And No of Quotation = 2 Average = 12/2=6 , But my result shown = 4 which is wrong :-

Hope some one can advise me.

Paul Yeo

[rubenmarin]

Hi, the file is in "(Virus scan in progress)" state, not allowing to download. 

And the script alone is not enough to reproduce. What is =$(ColumnDim501)? and [Pls select 61st Dim]? $(ColumnDim61)?

[paulyeo11]

Hi Sir

Thank you for wanted to help.

https://www.dropbox.com/sh/1f598j0n6yjl1iq/AABHTbf_XIjb3d-Nlnow7kuCa?dl=0

Hope you can download from above link.

Accounts_001:

LOAD * INLINE [
SOURCE,DocNum,Total,Customer_Name
A,1,3,SERVO ENGINEERING SDN BHD
A,1,4,SERVO ENGINEERING SDN BHD
A,2,5,SERVO DYNAMICS SDN BHD

];

WhatIfDimension:
LOAD * INLINE [
Pls select 61st Dim,Column61
Total_Amount,Total
Ave_SQ_Amt,Total
Total_SQ,DocNum

];

For Each i in 501
WhatIfDimension$(i):
LOAD * INLINE [
Pls select $(i)st Dim,Column$(i)
SOURCE,SOURCE
SQ_Num,DocNum
Total,Total
Customer_Name,Customer_Name

];
NEXT i;

[Kushal_Chawda · Accepted solution]

@paulyeo11  This is because when you are selecting any value from "[Pls select 61st Dim]", you are getting single column name in variable ColumnDim61 and your average calculation involves two column. So currently your calculation is  sum(Total)/Count(distinct Total)  which is 12/3 =4.

To get the correct result you need to change the expression of your average as below

sum($(=only({<[Pls select 61st Dim],Column61={'Total'}>}Column61)))/count(DISTINCT $(=only({<[Pls select 61st Dim],Column61={'DocNum'}>}Column61)) )

[paulyeo11]

Hi Sir

Thank you very much for your help.

Paul

[Kushal_Chawda]

@paulyeo11  please accept solution if it is what you need