Hi All,
I have below requirement,
Credit Limit – Open AR should give you the Open Credit of the customer .
If Open Credit > Credit Limit Amount then it is Over credit Limit Else it is Under Credit Limit
Add a filter “All” & Over credit Limit to select above options in the report
I have implemented that in script level and in expression level as per below,
Script level calculation,
QUALIFY COMPANYID,LineofBusiness,CustomerName,"LOBDescription",GrossAmount,HIER_ID;
Credit_Evaluation:
LOAD
COMPANYID,
HIER_ID,
COMPANYID&''&HIER_ID as %Joinkey,
COMPANYID&''&HIER_ID as test,
NationalAccount,
ParentAccount,
ProjectNo_3B11,
// PROJECTNO_SELL,
// PROJECTNO_ACTUAL,
CustomerNumber,
CustomerName,
//CustomerNumber &' - '& CustomerName as [Cust#_Name],
LineofBusiness,
LOBDescription,
CreditLimit,
OpenAR,
// SellingPrice,
Actual,
// BCIP,
Over_Under_Credit_Limit,
// ParentBusinessUnit,
CreditLimit_USD ,
OpenAR_USD,
Over_Under_Credit_Limit_USD,
Currency,
TotalReserve,
TotalReserve_USD,
OpenARageingDate,
// diff,
// Busket,
// Total_Credit_memos,
Total_Credit_memos_USD,
// Total_write_offs,
Total_write_offs_USD,
// Total_Billing,
Total_Billing_Usd,
// GrossAmount,
GrossAmount_USD,
DocumentType,
DocumentNo,
PKey,
NAE,
CreditLimit_USD - OpenAR as Open_Credit_Of_Cust,
//if(CreditLimit-OpenAR>CreditLimit_USD,'OverCredit','UnderCredit') as CRD_Limit_Status,
If(Match(LineofBusiness, '0001', '0002', '0003'), 1, 0) as _Flag_LOB_010203,
If(Match(LineofBusiness, '0004', '0005'), 1, 0) as _Flag_LOB_0405,
If(Match(LineofBusiness,'0006'), 1, 0) as _Flag_LOB_06
// 1 as Credit_Counter
// if(Over_Under_Credit_Limit_USD<CreditLimit_USD,Over_Under_Credit_Limit_USD) as Net_CustomerExposure,
// if(Over_Under_Credit_Limit_USD> CreditLimit_USD, Over_Under_Credit_Limit_USD) as Open_Credit
FROM
[..\QVD\Extract\Credit_Evaluation.qvd]
(qvd)
WHERE
Match(UPPER(TRIM(COMPANYID)), '10140','10426','10520','10622','11094','11150','11338')
And
Match(LineofBusiness, '0001', '0002', '0003', '0004', '0005', '0006');
Left Join(Credit_Evaluation)
Calc:
LOAD
//COMPANYID&''&HIER_ID as %JoinKey,
CustomerNumber ,
sum(OpenAR_USD) as Calculation
Resident Credit_Evaluation
Group by(CustomerNumber);
Left Join(Credit_Evaluation)
Open_Credit:
load
CustomerNumber,
if(CreditLimit_USD-Calculation>CreditLimit_USD,1,0) as CRD_Limit_Status,
CustomerNumber as [Cust#_Name]
Resident Credit_Evaluation;
Expression level calc
For all cust-
Round(Count( CustomerNumber))
For over_Credit_Limit_Cust-
Round(count({<CRD_Limit_Status = {'1'}>} CustomerNumber))
I am getting customer count as per above condition.
But in detail level I have customer chart and in that I need to show customer as per above expressions count.
If count is 2 then in detail level 2 custno should be display
If count is 10 then in detail level 10 custno should be display.
Can you please suggest how to proceed.
PS-Custno is dimension that I need to use in detail level.
Thanks
