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
);

Hi,

I want to upgrade my existing version of Qlik Sense Enterprise from release February 2023 to February 2024. The existing version still uses the PostgreSQL 9.6 database. So I want to use the Qlik PostgreSQL Installer to upgrade and unbundle the PostgreSQL database.

I stopped all Qlik services except the Qlik Sense Repository Database. Then I start the QPI tool and at the first screen it displays 'No supported existing Qlik Sense PostgreSQL database found. This program will install a new PostgreSQL v14.8 database".

Why doesn't this QPI tool detect my existing database?

When I press next and fill out all fields, field 'database port' becomes red with message 'Database port already used by an external service in the system'. Port 4432 is used by the postgres.exe so then it detects?!

I have attached the pg_hba.conf and the postgresql.conf from folder "C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6". Files are renamed with .txt to be able to upload.

Also I have tried to change the 'listen_addresses' line to 'listen_addresses = '*' without any luck.

Hi There,I have n number of columns in the table and I would like to apply rank function on below variables based on latest survey date where i can filter out rank = 1.

Appreciate your advice.

Something similar to SQL Query.

rank() over (partition by COUNTRY, STATE order by survery_date desc)

from table

where rank = 1