Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paolojolly
Creator
Creator

Expression in pivot table without using Aggr

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

1 Reply
sunny_talwar

I don't think this is possible