3 Replies Latest reply: Jan 26, 2017 3:14 AM by Vineeth Pujari RSS

    Master Calendar

    Abu Sayeed Baseer

      Hello Everyone.

       

      I need to create a visualization for Actual sales (sum(net_sale_value) - sum(net_sale_return_value) using a Master Calendar  (year, quarter, month, weekyear, weekdays, days). Can any one -please helo me i am still learning not able to understand how to do it. tried so many ways by reffering the Community users solutions. but failed.

       

      [SALES_HDR]:

      LOAD

        SLXACT#,

      BRANCH_CD,

        SALE_DATE ,

        NET_SALE_VALUE,

         VNDR_GROUP,

        [SALESMAN#] ;

       

      [SALES_HDR]:

      SELECT "SLXACT#",

        "BRANCH_CD",

        "SALE_DATE",

        "CUST#",

          NET_SALE_VALUE,

          VNDR_GROUP,

        "SALESMAN#"

      FROM "MBTW"."SALES_HDR" WHERE SALE_DATE BETWEEN '2016-01-01' AND '2017-12-31';

       

       

       

      [SALES_RETURN_HDR]:

      LOAD

        [SRXACT#],

          BRANCH_CD,

         SALE_RETURN_DATE,

         [SALESMAN#],

          VNDR_GROUP,

          NET_SALE_RETURN_VALUE;

       

       

      [SALES_RETURN_HDR]:

      SELECT "SRXACT#",

        "BRANCH_CD",

        "SALE_RETURN_DATE",

          NET_SALE_RETURN_VALUE,

          VNDR_GROUP,

         "SALESMAN#"

      FROM "MBTW"."SALES_RETURN_HDR" WHERE SALE_RETURN_DATE BETWEEN '2016-01-01' AND '2017-12-31';

       

       

       

      [VNDR_DTL]:

      LOAD

      VNDR_GROUP,

        TRANS_TYPE,

        LAT_DESC;

       

      [VNDR_DTL]:

      SELECT "VNDR_GROUP",

        "TRANS_TYPE",

        "LAT_DESC"

      FROM "MBTW"."VNDR_DTL" WHERE TRANS_TYPE='1';

       

       

       

       

       

       

      [BRANCHES]:

      LOAD BRANCH_CD ,

        BRANCH_E_NAME,

        BRANCH_TYPE;

       

       

      [BRANCHES]:

      SELECT "BRANCH_CD",

        "BRANCH_E_NAME",

        "BRANCH_TYPE"

      FROM "MBTW"."BRANCHES" WHERE BRANCH_TYPE='1' AND BRANCH_E_NAME NOT IN ('HEAD OFFICE', 'MAIN WAREHOUSE','EXPORT DIVISION');

       

       

      [CUSTOMER]:

      LOAD

          [CUST#] AS CSLM#,

      // CUST#,

          CUST_STATS,

        CUST_E_NAME,

        SALESMAN_FLAG;

       

       

      [CUSTOMER]:

      SELECT "CUST#",

        "CUST_STATS",

        "CUST_E_NAME",

        "SALESMAN_FLAG"

       

      FROM "MBTW"."CUSTOMER" WHERE CUST_STATS='A' AND SALESMAN_FLAG='Y';