Hi,

I want to sum of all Values for which ValueCategoryGlobal are "06-CIP Cold Water Consumption" and "06-CIP Hot water Consumption" and BatchTypeGlobal starts with "CIP". What is worng with my expression.

Could somone help ?

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)));
    
 NETWORK_FAULTS_CUSA_STRATEGY_POPULATION:  
 SQL
WITH UG_ES_CTE AS (
    SELECT ES.EQUIP_NO,
        ES.PROFILE_FY,
        ES.INCDNT_DT,
        ES.POF_INCD_NO,
        1 AS UG_ELEK_SHK
    FROM ES_TABLE ES
    WHERE UPPER(TRIM(ES.ASSET_CAT_DESC)) = ('UG SERVICE CONNECTIONS')
        AND ES.ELEC_SHOCK = 1
        AND ES.CURR_FLG = 'Y'
),
UG_ES_CTE1 AS (
    SELECT PROFILE_FY,
        SUM(UG_ELEK_SHK) AS TOTAL_EL_SHK
    FROM UG_ES_CTE
    GROUP BY PROFILE_FY
    ORDER BY PROFILE_FY DESC
),
EQUIP_TMV_PILLAR_UNIONS 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
        AND CURR_FLG = 'Y'
        AND EQUIP_STAT = 'AC'
        AND TRIM(EQUIP_GRP_ID) = 'PILL'
    UNION ALL
    SELECT '$(vYear_Previous_2)' || '/' || '$(vYear_Previous_1)' as FY_YEAR_SNAPSHOT,
        1 AS PILLAR_COUNT
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_1_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        AND EQUIP_STAT = 'AC'
        AND TRIM(EQUIP_GRP_ID) = 'PILL'
    UNION ALL
    SELECT '$(vYear_Previous_3)' || '/' || '$(vYear_Previous_2)' as FY_YEAR_SNAPSHOT,
        1 AS PILLAR_COUNT
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_2_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        AND EQUIP_STAT = 'AC'
        AND TRIM(EQUIP_GRP_ID) = 'PILL'
    UNION ALL
    SELECT '$(vYear_Previous_4)' || '/' || '$(vYear_Previous_3)' as FY_YEAR_SNAPSHOT,
        1 AS PILLAR_COUNT
    FROM ETD_TABLE
    WHERE TO_DATE('$(current_3_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        AND EQUIP_STAT = 'AC'
        AND TRIM(EQUIP_GRP_ID) = 'PILL'
),
EQUIP_TMV_PILLAR_AGG AS (
    SELECT FY_YEAR_SNAPSHOT,
        SUM(PILLAR_COUNT) AS TOTAL_PILLARS
    FROM EQUIP_TMV_PILLAR_UNIONS
    GROUP BY FY_YEAR_SNAPSHOT
    ORDER BY FY_YEAR_SNAPSHOT DESC
),
UG_SHOCK_KPI AS (
    SELECT 'Underground' AS CUSA_STRATEGY,
        E.FY_YEAR_SNAPSHOT,
        E.TOTAL_PILLARS AS POP_COUNT,
        S.TOTAL_EL_SHK,
        (S.TOTAL_EL_SHK) / (E.TOTAL_PILLARS) AS CUSA_KPI
    FROM EQUIP_TMV_PILLAR_AGG E
        JOIN UG_ES_CTE1 S ON S.PROFILE_FY = E.FY_YEAR_SNAPSHOT
    ORDER BY FY_YEAR_SNAPSHOT DESC
),
CUSA_CTE_UNION AS (
    SELECT EQUIP_NO,
        PICK_ID,
        INSTLN_DT,
        '$(vYear_Previous_1)' || '/' || '$(vYear_Current)' as FY_YEAR_SNAPSHOT,
        CUSA_CARRY_OVER_POLE_CLMP_TYP,
        CUSA_MCB_SVC_CBL_CLMP_AT_POA,
        CUSA_CNSMR_POLE_CLMP_TYP,
        CUSA_ST_MAINS_POLE_CLMP_TYP
    FROM NAME_TABLE
    WHERE TO_DATE('$(current_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        AND EQUIP_STAT_CDE = 'AC'
        AND CURR_FLG = 'Y'
        AND EQUIP_GRP_ID = 'CUSA'
    UNION ALL
    SELECT EQUIP_NO,
        PICK_ID,
        INSTLN_DT,
        '$(vYear_Previous_2)' || '/' || '$(vYear_Previous_1)' as FY_YEAR_SNAPSHOT,
        CUSA_CARRY_OVER_POLE_CLMP_TYP,
        CUSA_MCB_SVC_CBL_CLMP_AT_POA,
        CUSA_CNSMR_POLE_CLMP_TYP,
        CUSA_ST_MAINS_POLE_CLMP_TYP
    FROM NAME_TABLE
    WHERE TO_DATE('$(current_1_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        AND EQUIP_STAT_CDE = 'AC'
        AND CURR_FLG = 'Y'
        AND EQUIP_GRP_ID = 'CUSA'
    UNION ALL
    SELECT EQUIP_NO,
        PICK_ID,
        INSTLN_DT,
        '$(vYear_Previous_3)' || '/' || '$(vYear_Previous_2)' as FY_YEAR_SNAPSHOT,
        CUSA_CARRY_OVER_POLE_CLMP_TYP,
        CUSA_MCB_SVC_CBL_CLMP_AT_POA,
        CUSA_CNSMR_POLE_CLMP_TYP,
        CUSA_ST_MAINS_POLE_CLMP_TYP
    FROM NAME_TABLE
    WHERE TO_DATE('$(current_2_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        AND EQUIP_STAT_CDE = 'AC'
        AND CURR_FLG = 'Y'
        AND EQUIP_GRP_ID = 'CUSA'
    UNION ALL
    SELECT EQUIP_NO,
        PICK_ID,
        INSTLN_DT,
        '$(vYear_Previous_4)' || '/' || '$(vYear_Previous_3)' as FY_YEAR_SNAPSHOT,
        CUSA_CARRY_OVER_POLE_CLMP_TYP,
        CUSA_MCB_SVC_CBL_CLMP_AT_POA,
        CUSA_CNSMR_POLE_CLMP_TYP,
        CUSA_ST_MAINS_POLE_CLMP_TYP
    FROM NAME_TABLE
    WHERE TO_DATE('$(current_3_fy_Date)', 'DD-MM-YYYY') BETWEEN EFEC_STRT_DT AND EFEC_END_DT
        AND EQUIP_STAT_CDE = 'AC'
        AND CURR_FLG = 'Y'
        AND EQUIP_GRP_ID = 'CUSA'
),
CUSA_CTE AS (
    SELECT U.*,
        CASE
            WHEN (
                LOWER(CUSA_CARRY_OVER_POLE_CLMP_TYP) = 'helical clamp'
                OR LOWER(CUSA_MCB_SVC_CBL_CLMP_AT_POA) = 'helical clamp'
                OR LOWER(CUSA_CNSMR_POLE_CLMP_TYP) = 'helical clamp'
                OR LOWER(CUSA_ST_MAINS_POLE_CLMP_TYP) = 'helical clamp'
            ) THEN 'Steel Preformed Helical Clamp'
            WHEN (
                LOWER(CUSA_CARRY_OVER_POLE_CLMP_TYP) = 'wedge clamp'
                OR LOWER(CUSA_MCB_SVC_CBL_CLMP_AT_POA) = 'wedge clamp'
                OR LOWER(CUSA_CNSMR_POLE_CLMP_TYP) = 'wedge clamp'
                OR LOWER(CUSA_ST_MAINS_POLE_CLMP_TYP) = 'wedge clamp'
            )
            AND INSTLN_DT < TO_DATE ('20100101', 'yyyymmdd') THEN 'Wedge Clamp Installed Before 2010'
            WHEN (
                LOWER(CUSA_CARRY_OVER_POLE_CLMP_TYP) = 'wedge clamp'
                OR LOWER(CUSA_MCB_SVC_CBL_CLMP_AT_POA) = 'wedge clamp'
                OR LOWER(CUSA_CNSMR_POLE_CLMP_TYP) = 'wedge clamp'
                OR LOWER(CUSA_ST_MAINS_POLE_CLMP_TYP) = 'wedge clamp'
            )
            AND INSTLN_DT >= TO_DATE ('20100101', 'yyyymmdd') THEN 'Wedge Clamp Installed On/After 2010'
            ELSE 'Other Clamp Types'
        END AS CUSA_STRATEGY,
        1 AS POP_COUNT
    FROM CUSA_CTE_UNION U
),
ES_CTE AS (
    SELECT C.*,
        ES.INCDNT_DT,
        UPPER(ES.POF_INCD_NO) AS POF_INCD_NO
    FROM CUSA_CTE C
        JOIN ES ON ES.EQUIP_NO = C.EQUIP_NO
        AND TRIM(ASSET_GRP_DESC) = 'Service Connections'
        AND ELEC_SHOCK = 1
        AND INCDNT_DT >= TO_DATE('20160101', 'yyyymmdd')
        AND ES.EQUIP_STAT = 'AC'
        AND ES.CURR_FLG = 'Y'
        AND ES.POF_INCD_NO IS NOT NULL
),
INCIDENT_DM_CTE AS (
    SELECT DISTINCT IDF.NMI,
        UPPER(IDF.INCIDENT_REF) AS INCIDENT_REF,
        ICD.INCDNT_CAT_NAM
    FROM  IDF_TABLE
        JOIN  ICD_TABLE ON IDF.INCIDENT_CATEGORY_SK = ICD.INCDNT_CAT_SK
        AND ICD.INCDNT_CAT_ID = 'ES'
    WHERE IDF.YR_NO >= 2016
        AND TRIM(IDF.NMI) != 'N/A'
        AND IDF.INCIDENT_REF IS NOT NULL
),
INCIDENT_ELCK_SHK AS (
    SELECT E.CUSA_STRATEGY,
        E.EQUIP_NO,
        E.PICK_ID,
        E.INCDNT_DT,
        E.FY_YEAR_SNAPSHOT,
        1 AS EL_SK_COUNT
    FROM ES_CTE E
        JOIN INCIDENT_DM_CTE IDF ON E.POF_INCD_NO = IDF.INCIDENT_REF 
),
CUSA_POP_CTE AS (
    SELECT CUSA_STRATEGY AS CUSA_STRATEGY_POP,
        FY_YEAR_SNAPSHOT,
        SUM(POP_COUNT) AS POP_COUNT
    FROM CUSA_CTE
    GROUP BY CUSA_STRATEGY,
        FY_YEAR_SNAPSHOT
),
CUSA_ELEC_SHK AS (
    SELECT CUSA_STRATEGY,
        FY_YEAR_SNAPSHOT,
        SUM(EL_SK_COUNT) AS TOTAL_EL_SHK
    FROM INCIDENT_ELCK_SHK
    GROUP BY CUSA_STRATEGY,
        FY_YEAR_SNAPSHOT
),
CUSA_SHK_KPI AS (
    SELECT E.CUSA_STRATEGY,
        E.FY_YEAR_SNAPSHOT,
        P.POP_COUNT,
        E.TOTAL_EL_SHK,
        (E.TOTAL_EL_SHK) / (P.POP_COUNT) AS CUSA_KPI
    FROM CUSA_POP_CTE P
        JOIN CUSA_ELEC_SHK E ON P.FY_YEAR_SNAPSHOT = E.FY_YEAR_SNAPSHOT
        AND P.CUSA_STRATEGY_POP = E.CUSA_STRATEGY
    ORDER BY P.FY_YEAR_SNAPSHOT DESC
),
UG_CUSA_KPI_JOIN AS(
    SELECT UG.*
    FROM UG_SHOCK_KPI UG
    UNION ALL
    SELECT CUSA.*
    FROM CUSA_SHK_KPI CUSA
)
SELECT *
FROM UG_CUSA_KPI_JOIN
ORDER BY FY_YEAR_SNAPSHOT DESC;