Forum Discussion

PriyankaA905's avatar
PriyankaA905
Frequent Visitor
4 years ago

Lag Report

Hi,    I have to create a lag repot in below format In this case, Value 14,241 no of accounts were opened as on 30 April 18, and are active in April 2018. 14016 no of accounts were opened a...
Need Help
Tips and Tricks
PriyankaA905's avatar
PriyankaA905
Frequent Visitor
4 years ago

We dont have separate Date Dim . The date is in same table as open and close data

The data is related to bank. We have below columns in one table

Column nameDescription
ACNTS_CLIENT_NUMClient number
IACLINK_ACTUAL_ACNUMActual Account number
ACNTS_OPENING_DATEOpening date
ACNTS_CLOSURE_DATEClosing date
AS_ON_DATEBalances as on date

 

  • PriyankaA905's avatar
    PriyankaA905
    Frequent Visitor
    4 years ago

    Please refer to below image for better understanding

    Column namemeaning
    ACNTS_BRN_CODEBranch code for a specific account
    ACNTS_CLIENT_NUMcustomer number
    IACLINK_ACTUAL_ACNUMCustomer's account number.
    ACNTS_PROD_CODEProduct code with respect to the transaction 
    ACNTS_AC_TYPEAccounts type code
    ACNTS_OPENING_DATEAcoount opening date
    ACNTS_CLOSURE_DATEAccount closing date
    AS_ON_DATEMonth end date
    CURRENT_BALANCE_ASONBalance as on last date of month
    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      PriyankaA905 

       

      The rows and columns in this matrix correspond to which date column you have, and what logic is used to calculate the values in the matrix, and which column number is calculated, you have to make it clear.

      Please do not take screenshots, share sample data in table format or link, otherwise we can't replicate the test data.

      Thank you.

       

      Best Regards,
      Community Support Team _ Janey

       

      • PriyankaA905's avatar
        PriyankaA905
        Frequent Visitor
        4 years ago

        April 2018, March 2018,... we have made duplicate column of AS_ON_DATE with Month and year in it

        30 April 2018, 31 May 2018, ... is a AS_ON_DATE column

        We have used distinct values of IACLINK_ACTUAL_ACNUM for values

         

        ACNTS_BRN_CODEACNTS_CLIENT_NUMIACLINK_ACTUAL_ACNUMACNTS_PROD_CODEACNTS_AC_TYPEACNTS_OPENING_DATEACNTS_CLOSURE_DATEAS_ON_DATECURRENT_BALANCE_ASON
        5111222001010010100014501101552016/21/2017 12:00:00 AMnull2/28/2021 12:00:00 AM40
        5112222001110010100014501112552026/21/2017 12:00:00 AMnull2/28/2021 12:00:00 AM10800
        51132220012100101000145011235050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM1250
        51142220013100101000145011335050112/24/2020 12:00:00 AM12/24/2020 12:00:00 AM2/28/2021 12:00:00 AM100
        51152220014100101000145011435050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM100
        51162220015100101000145011535050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM5350
        51172220016100101000145011635050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM100
        51182220017100101000145011735050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM3800
        51192220018100101000145011835050112/24/2020 12:00:00 AMnull2/28/2021 12:00:00 AM3800
        51202220019100101000145011935050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM100
        51212220020100101000145012035050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM5700
        51222220021100101000145012135050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM100
        51232220022100101000145012235050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM100
        51242220023100101000145012335050112/24/2020 12:00:00 AMnull3/31/2021 12:00:00 AM1250
        51252220024100101000145012435050112/24/2020 12:00:00 AM10/3/2021 0:003/31/2021 12:00:00 AM8900
        51262220025100101000145012535050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM3050
        51272220026100101000145012635050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM6200
        51282220027100101000145012735050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM2
        51292220028100101000145012835050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM390
        51302220029100101000145012935050112/28/2020 12:00:00 AMnull3/31/2021 12:00:00 AM6200
        5111222001010010100014501101552016/21/2017 12:00:00 AMnull4/30/2021 12:00:00 AM40
        5112222001110010100014501112552026/21/2017 12:00:00 AMnull4/30/2021 12:00:00 AM10800
        51132220012100101000145011235050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM1250
        51152220014100101000145011435050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM100
        51162220015100101000145011535050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM5350
        51172220016100101000145011635050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM100
        51182220017100101000145011735050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM3800
        51192220018100101000145011835050112/24/2020 12:00:00 AMnull4/30/2021 12:00:00 AM3800