BFL Function #2 : working with YEAROVERYEARDIFFERENCE function

[Former Member]

Hello Everyone,

                here I am demonstrating the BFL function “YEAROVERYEARDIFFERENCE” to Calculate Year over year difference in Sales Amount

My Database table “INTERNETSALES” contains approximately 60,000 records

I have a Date Column “ORDERDATEKEY” and a Measure column “SALESAMOUNT”

1^st I Aggregated the data year wise and then I applied the above BFL function

This is my Year wise Aggregated data…

SELECT YEAR("ORDERDATEKEY") AS "YEAR",

       SUM("SALESAMOUNT") AS "SALESAMOUNT"

    FROM "HANAGA"."INTERNETSALES"

    GROUP BY YEAR("ORDERDATEKEY");

--BFL Function "YEAROVERYEARDIFFERENCE"

--to Calculate Year Over Year Difference in Sales Amount

CREATE PROCEDURE "HANAGA"."BFL_YOYD_EXAMPLE"

LANGUAGE SQLSCRIPT

READS SQL DATA

AS

BEGIN

tab1 = CE_COLUMN_TABLE( "HANAGA"."INTERNETSALES" );

tab2 = CE_PROJECTION(

          :tab1,

          ["ORDERDATEKEY",

                      "SALESAMOUNT",

           CE_CALC( ' leftstr(string("ORDERDATEKEY"),4) ', CHAR(4)) AS "YEAR"

          ]

       );

tab3 = CE_AGGREGATION(

             :tab2,

             [ SUM("SALESAMOUNT") AS "YEAR_WISE_AMT" ],

             [ "YEAR" ]   

       );

tab4 = CE_PROJECTION(

             :tab3,

             [ "YEAR","YEAR_WISE_AMT",

               CE_CALC( ' "YEAR_WISE_AMT" ', DOUBLE ) AS "AMT"

             ]   

       );

base_tab = SELECT "AMT" AS "BASE"                           --to extract only one column data

                FROM :tab4;                          --do not use CE_PROJECTION

style_tab =  SELECT TO_DOUBLE(1) AS "STYLE"     --only one Record required

                  FROM DUMMY;  

CALL "_SYS_AFL"."AFLBFL_YEAROVERYEARDIFFERENCE_PROC"(

                                    :base_tab,

                                    :style_tab,

                                    :result_tab

                                  );

out_tab = CE_VERTICAL_UNION(  :tab4, ["YEAR"],

                              :base_tab, ["BASE" AS "BASE AMOUNT"],

                              :result_tab, ["DIFFERENCE" AS "YOY DIFFERENCE"]

                           );

SELECT * FROM :out_tab;

END;

--Execute above Procedure

CALL "HANAGA"."BFL_YOYD_EXAMPLE";

Enjoy HANA

Regards

Nagababu Tubati