Hi everyone, I need help in pivot table expression.
In my table I have two dimensions (DIREZIONE, ENTE) and as expression I'm trying to use
(
if(sum(IF(ANNO_DOC = (2018-5) AND MAKEDATE((2018-5) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +
if(sum(IF(ANNO_DOC = (2018-4) AND MAKEDATE((2018-4) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +
if(sum(IF(ANNO_DOC = (2018-3) AND MAKEDATE((2018-3) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +
if(sum(IF(ANNO_DOC = (2018-2) AND MAKEDATE((2018-2) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +
if(sum(IF(ANNO_DOC = (2018-1) AND MAKEDATE((2018-1) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0)
)
But it doesn't work properly(totals in pivot are incorrect)
If I try to use
sum(aggr (
(
if(sum(IF(ANNO_DOC = (2018-5) AND MAKEDATE((2018-5) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +
if(sum(IF(ANNO_DOC = (2018-4) AND MAKEDATE((2018-4) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +
if(sum(IF(ANNO_DOC = (2018-3) AND MAKEDATE((2018-3) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +
if(sum(IF(ANNO_DOC = (2018-2) AND MAKEDATE((2018-2) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +
if(sum(IF(ANNO_DOC = (2018-1) AND MAKEDATE((2018-1) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3) , W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0)
)
,D_DIREZIONE_FLD, C_ENTE_DEST))
Works properly(totals in pivot are correct)
I would like to use first solution because i want to create a global variable for using in different pivot table that can have different dimensions(without having to change dimensions of the function Aggr for each pivot table)
Can someone help me?
thanks
