Showing detail level customer as per count of chart level count of customers

[deepakqlikview_123]

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

[deepakqlikview_123]

Hi All,

Can you please suggest on this.

Thanks