Iterate to extract the data in the future

[CK_WAKE]

Hi There,

I have an SQL query that runs in QLIK. I have included some variables to take a snapshot from the current fiscal date, month, and year up to the last four FY years and append the tables to one table and with some conditions. If the stakeholders view this report in 2030, they should be able to view the data from 2020 till 2030, not just latest four years (Example: If stakeholders view the report in the year 2030, they should be able to view the data from 2030 till 2020). I am not sure how I can fit in all the below SQL query to loop through.

I appreciate your help on this.

/* From current to last 4 FY years */
Let vYear_Current = Year(Today());
LET vYear_Previous_1 = Year(Today()) -1;
LET vYear_Previous_2 = Year(Today()) -2;
LET vYear_Previous_3 = Year(Today()) -3;
LET vYear_Previous_4 = Year(Today()) -4;
LET vYear_Previous_5 = Year(Today()) -5;

/* From current to last 4 FY snapshot date */

let current_fy_Date = MonthStart(DayName(YearEnd(today(),-1, 8)));
let current_1_fy_Date = MonthStart(DayName(YearEnd(today(),-2, 8)));
let current_2_fy_Date = MonthStart(DayName(YearEnd(today(),-3, 8)));
let current_3_fy_Date = MonthStart(DayName(YearEnd(today(),-4, 8)));
let current_4_fy_Date = MonthStart(DayName(YearEnd(today(),-5, 8)));
    
 POP:  
 SQL
WITH DF1 AS (
    SELECT * 
    FROM ES_TABLE ES
    WHERE 
),
DF2 AS (
    SELECT * FROM UG_ES_CTE
    GROUP BY FY
    ORDER BY FY DESC
),
DF3 AS (
    SELECT '$(vYear_Previous_1)' || '/' || '$(vYear_Current)' as FY_YEAR_SNAPSHOT,
    FROM  ETD_TABLE
    WHERE TO_DATE('$(current_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        
    UNION ALL
    SELECT '$(vYear_Previous_2)' || '/' || '$(vYear_Previous_1)' as FY_YEAR_SNAPSHOT,
        
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_1_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
            UNION ALL
    SELECT '$(vYear_Previous_3)' || '/' || '$(vYear_Previous_2)' as FY_YEAR_SNAPSHOT,
      
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_2_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
      
    UNION ALL
    SELECT '$(vYear_Previous_4)' || '/' || '$(vYear_Previous_3)' as FY_YEAR_SNAPSHOT,
       
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_3_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
       
),
DF4 AS (
    SELECT FY_YEAR_SNAPSHOT,
        SUM(SALES) AS TOTAL_SALES
    FROM UNIONS
    GROUP BY FY_YEAR_SNAPSHOT
    ORDER BY FY_YEAR_SNAPSHOT DESC
),
KPI AS (
    SELECT 'Env' AS STRATEGY,
        E.FY_YEAR_SNAPSHOT,
        E.TOTAL_SALES AS POP_SALES,
        FROM DF4 E
        JOIN DF1 S ON S.PROFILE_FY = E.PROFILE_FY
    ORDER BY FY_YEAR_SNAPSHOT DESC
);

[steeefan · Accepted solution]

That's probably best done by a loop, such as this:

LET vBaseYear = 2020;

FOR vYear = $(vBaseYear) TO Year(Today())

  TRACE $(vYear);

  LET vFYDate = MakeDate($(vYear), 7, 1);
  TRACE $(vFYDate);

  // Your SQL code, adapted
    
NEXT vYear;

This will calculcate every vFYDate value between vBaseYear and today's year. In the loop, you could easily then reference one year forward or backward, as you do here '$(vYear_Previous_4)' || '/' || '$(vYear_Previous_3)', and peform all your calculcations and queries. This will also shortend your code significantly.

[CK_WAKE · Accepted solution]

I really appreciate your help with this. It worked perfectly.

[CK_WAKE]

Hi There,

I am trying to cut shot the query as you suggested by removing the Union all function. However, it is producing the snap shot of Fy year of 2023/2024 only and I can see it it looping the 2023/2014 4 times and not looping till min year of 2020 .

Appreciate you advice on this.

LET vBaseYear = 2020; 
LET vCurrentYear = Year(Today()); 
FOR vYear = $(vBaseYear) TO $(vCurrentYear) 
TRACE $(vYear); // track the the years iterate


LET vFYDate = MakeDate($(vYear), 7, 1); 
  TRACE $(vFYDate); 
  
LIB Connect To '$(vOracleConn)';
/* To select FY Period for the last 5 FY years */
Let vYear_Current = $(vCurrentYear);
LET vYear_Previous_1 = $(vCurrentYear) -1;
LET vYear_Previous_2 = $(vCurrentYear) -2;
LET vYear_Previous_3 = $(vCurrentYear) -3;
LET vYear_Previous_4 = $(vCurrentYear) -4;
LET vYear_Previous_5 = $(vCurrentYear) -5;

/* To select FY date for the last 5 FY years */

let current_fy_Date = MonthStart(DayName(YearEnd(today(),-1, 8)));
let current_1_fy_Date = MonthStart(DayName(YearEnd(today(),-2, 8)));
let current_2_fy_Date = MonthStart(DayName(YearEnd(today(),-3, 8)));
let current_3_fy_Date = MonthStart(DayName(YearEnd(today(),-4, 8)));
let current_4_fy_Date = MonthStart(DayName(YearEnd(today(),-5, 8)));
    
 NETWORK_FAULTS_CUSA_STRATEGY_POPULATION:  
 SQL
/* From current to last 4 FY years */
Let vYear_Current = Year(Today());
LET vYear_Previous_1 = Year(Today()) -1;
LET vYear_Previous_2 = Year(Today()) -2;
LET vYear_Previous_3 = Year(Today()) -3;
LET vYear_Previous_4 = Year(Today()) -4;
LET vYear_Previous_5 = Year(Today()) -5;

/* From current to last 4 FY snapshot date */

let current_fy_Date = MonthStart(DayName(YearEnd(today(),-1, 8)));
let current_1_fy_Date = MonthStart(DayName(YearEnd(today(),-2, 8)));
let current_2_fy_Date = MonthStart(DayName(YearEnd(today(),-3, 8)));
let current_3_fy_Date = MonthStart(DayName(YearEnd(today(),-4, 8)));
let current_4_fy_Date = MonthStart(DayName(YearEnd(today(),-5, 8)));
    
 POP:  
 SQL
WITH DF1 AS (
    SELECT * 
    FROM ES_TABLE ES
    WHERE 
),
DF2 AS (
    SELECT * FROM UG_ES_CTE
    GROUP BY FY
    ORDER BY FY DESC
),
DF3 AS (
    SELECT '$(vYear_Previous_1)' || '/' || '$(vYear_Current)' as FY_YEAR_SNAPSHOT,
    FROM  ETD_TABLE
    WHERE TO_DATE('$(current_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        
    UNION ALL
    SELECT '$(vYear_Previous_2)' || '/' || '$(vYear_Previous_1)' as FY_YEAR_SNAPSHOT,
        
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_1_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
            UNION ALL
    SELECT '$(vYear_Previous_3)' || '/' || '$(vYear_Previous_2)' as FY_YEAR_SNAPSHOT,
      
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_2_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
      
    UNION ALL
    SELECT '$(vYear_Previous_4)' || '/' || '$(vYear_Previous_3)' as FY_YEAR_SNAPSHOT,
       
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_3_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
       
),
DF4 AS (
    SELECT FY_YEAR_SNAPSHOT,
        SUM(SALES) AS TOTAL_SALES
    FROM UNIONS
    GROUP BY FY_YEAR_SNAPSHOT
    ORDER BY FY_YEAR_SNAPSHOT DESC
),
KPI AS (
    SELECT 'Env' AS STRATEGY,
        E.FY_YEAR_SNAPSHOT,
        E.TOTAL_SALES AS POP_SALES,
        FROM DF4 E
        JOIN DF1 S ON S.PROFILE_FY = E.PROFILE_FY
    ORDER BY FY_YEAR_SNAPSHOT DESC
);

NEXT vYear; 
exit script;

[steeefan · Accepted solution]

You are still using your old variables in your script:

/* To select FY Period for the last 5 FY years */
Let vYear_Current = $(vCurrentYear);
LET vYear_Previous_1 = $(vCurrentYear) -1;
//..

/* To select FY date for the last 5 FY years */
let current_fy_Date = MonthStart(DayName(YearEnd(today(),-1, 8)));
let current_1_fy_Date = MonthStart(DayName(YearEnd(today(),-2, 8)));
//..

They are not reacting to the loop but are statically defined. You need to change these variables in respect to the values created in the loop.

[CK_WAKE]

Thanks for the help.