topic Re: How to implement CY and PY in the same row in App Development

How to implement CY and PY in the same row

aresb — Wed, 01 Feb 2023 15:27:52 GMT

Hi everyone,

I'm trying to implement a simple set analysis where the user choose a year and the table should appear like this:

However, when i implement the below set expression and selecting year 2022 as filter i have this,

SUM({$<cod_year={$(=MAX(cod_year)-1)}>}Measures)

i have this results:

Is there a way to achieve the result in the first image?

Even via data load editor

Re: How to implement CY and PY in the same row

QFabian — Wed, 01 Feb 2023 16:18:55 GMT

Hi @aresb you can try creating a chart table with 3 expressions, no dimensions.

Please check if this example works for you.

Script:

Load * INLINE [
cod_year, Measures
2022, 3000
2021, 5000
];

exit script;

chart

Year = only(cod_year)

Measures CY = SUM(Measures)

Measures PY = SUM({$<cod_year={$(=MAX(cod_year)-1)}>}Measures)

Re: How to implement CY and PY in the same row

vinieme12 — Thu, 02 Feb 2023 03:17:04 GMT

As below

Dimension

= cod_year

Measure

CY = sum({<cod_year={"$(=MAx(cod_year))"}>}Measures)

PY = sum(aggr(sum({<cod_year={"$(=MAx(cod_year)-1)"}>} TOTAL Measures),cod_year))

Re: How to implement CY and PY in the same row

aresb — Wed, 15 Feb 2023 14:00:20 GMT

i 've solved this way in the data load editor

Measure_CY: LOAD "Year", "Month", Measures as Measure_CY, Date#("Year" & "Month",'YYYYMM') as id_date, Date#("Year" & "Month"+100,'YYYYMM') as id_date_1 FROM [lib://m/Misura PY qlik.xlsx] (ooxml, embedded labels, table is Foglio1); NoConcatenate[Measure_CY]: Load Date#(id_date_1,'YYYYMM') as id_date, Measure_CY as Measure_PY Resident Measure_CY WHERE YEAR(Date(id_date_1,'YYYYMM')) <= YEAR(now()); drop Field id_date_1

which give me this