How to implement CY and PY in the same row

[aresb]

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

[QFabian]

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)

[vinieme12]

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

[aresb]

Hi

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