How to insert CY and PY in the same pivot table

[aresb]

Hi everyone, i'm trying to reproduce this table from an old BI tool

So in qlik, i created a pivot table and i've added into column the dimension 'CY/PY' in this way

=Valuelist('CY', 'PY')

and i've built the measure in this way 

=

if(ValueList('CY','PY') = 'CY',
count(distinct(({<cod_year_month= {">=$(=min(cod_year_month))<=$(=max(cod_year_month))"}> }MY_MEASURE)))
,count(distinct(({<cod_year_month= {">=$(=min((DATE(addmonths(date#(cod_year_month,'YYYYMM'),-12),'YYYYMM'))))<=$(=max((DATE(addmonths(date#(cod_year_month,'YYYYMM'),-12),'YYYYMM'))))"}> }MY_MEASURE))))

which i supposed is correct.

The main problem is that i can't let this work and i have the same measure in CY and PY.

What am i doing wrong ?

[rubenmarin · Accepted solution]

Hi, try setting the Date outside the min() and max(), and the min() max() as the first function on the field:

{">=$(=Date(addmonths(date#(min(cod_year_month),'YYYYMM'),-12),'YYYYMM'))<=$(=Date(addmonths(date#(max(cod_year_month),'YYYYMM'),-12)),'YYYYMM'))"}

[aresb]

Thx for responding,

now i've this set expression 

which recognize correctly year and month as a date for PY, but still replicate the kpi along the table

i can't understand what im doing wrong

[rubenmarin]

Hi, it seems ok, if you can upload a sample with some dummy, scrambled or partioned data I could make some tests to check dthe expression needed.

[vinieme12]

Try using a Dummy Dimension instead

DummyDim:

Load Dual(Dim,so_Dim) as Dim

Load * Inline [

Dim,so_Dim

CY,1

PY,2

];

Then in pivot table measures use

=Pick(Dim,
//CY-1
,count(distinct({<cod_year_month= {">=$(=min(cod_year_month))<=$(=max(cod_year_month))"}> }MY_MEASURE)
//PY-2
,count(distinct {<cod_year_month = {">=$(=Date(addmonths(date#(min(cod_year_month),'YYYYMM'),-12),'YYYYMM'))<=$(=Date(addmonths(date#(max(cod_year_month),'YYYYMM'),-12)),'YYYYMM'))"} >} MY_MEASURE)
)