ERROR WITH PIVOT TABLE AND IF NESTED

[francesctesi]

hi,

I am trying to create a pivot table with a measure called 'actual', but this is the result at the moment, where the sum is not visible.

I specify that the key is the field COMMESSA  (in this case equal to "TE22415"),and the sub-category is called "MACRO TIPO COSTO" (1 Ricavi,3 personale ecc.....) and inside we find another dimension called  'TIPO COSTO ricavi,dipendenti,ecc).

This the script for the measure ACTUAL (sorry if is so complex):

if( 

[Tipo Costo]=('ricavi'),

    if((-[BGT RICAVI] + ((Sum( {$<[Macro Tipo Costo]={'1 Ricavi'},FLAG_DOC={'2'}>} IMPORTO ) - Sum( {$<[Macro Tipo Costo]-={'1 Ricavi'},FLAG_DOC={'2'}>} IMPORTO ))-

(Sum( {$<[Macro Tipo Costo]={'1 Ricavi'},COMMESSA={'TE','OC','AR'} >} IMPORTO ) - Sum( {$<[Macro Tipo Costo]-={'1 Ricavi'},COMMESSA={'TE','OC','AR'},FLAG_DOC={'2'}>} IMPORTO ) )))<0,

    ((-[BGT RICAVI]+((Sum( {$<[Macro Tipo Costo]={'1 Ricavi'},FLAG_DOC={'2'}>} IMPORTO ) - Sum( {$<[Macro Tipo Costo]-={'1 Ricavi'},FLAG_DOC={'2'}>} IMPORTO ))-

(Sum( {$<[Macro Tipo Costo]={'1 Ricavi'},COMMESSA={'TE','OC','AR'},FLAG_DOC={'2'} >} IMPORTO ) - Sum( {$<[Macro Tipo Costo]-={'1 Ricavi'},COMMESSA={'TE','OC','AR'},FLAG_DOC={'2'}>} IMPORTO ) ))))

    ,0

    )

        ,if([Tipo Costo]=('hardware'),if((Sum( {$<[Tipo Costo]={'hardware'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'hardware'},FLAG_DOC={'2'}>} IMPORTO ))>0,(Sum( {$<[Tipo Costo]={'hardware'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'hardware'},FLAG_DOC={'2'}>} IMPORTO )),0)

        ,

        if([Tipo Costo]=('fornitori'),if((Sum( {$<[Tipo Costo]={'fornitori'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'fornitori'},FLAG_DOC={'2'}>} IMPORTO ))>0,(Sum( {$<[Tipo Costo]={'fornitori'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'fornitori'},FLAG_DOC={'2'}>} IMPORTO )),0)

            ,

            if([Tipo Costo]=('licenze'),if((Sum( {$<[Tipo Costo]={'licenze'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'licenze'},FLAG_DOC={'2'}>} IMPORTO ))>0,(Sum( {$<[Tipo Costo]={'licenze'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'licenze'},FLAG_DOC={'2'}>} IMPORTO )),0)

                ,

                if([Tipo Costo]=('altro'),if((Sum( {$<[Tipo Costo]={'altro'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'altro'},FLAG_DOC={'2'}>} IMPORTO ))>0,(Sum( {$<[Tipo Costo]={'altro'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'altro'},FLAG_DOC={'2'}>} IMPORTO )),0)

                     ,

                      if([Tipo Costo]=('consumabili'),if((Sum( {$<[Tipo Costo]={'consumabili'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'consumabilii'},FLAG_DOC={'2'}>} IMPORTO ))>0,(Sum( {$<[Tipo Costo]={'consumabilii'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'consumabilii'},FLAG_DOC={'2'}>} IMPORTO )),0)

                        ,

                        if([Tipo Costo]=('trasferte'),if(GGAFINIRE*70>0,GGAFINIRE*70,0),

                        if([Tipo Costo]=('provvigioni'),if((Sum( {$<[Tipo Costo]={'provvigioni'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'provvigioni'},FLAG_DOC={'2'}>} IMPORTO ))>0,(Sum( {$<[Tipo Costo]={'provvigioni'},FLAG_DOC={'1'}>} IMPORTO ) - Sum( {$<[Tipo Costo]={'provvigioni'},FLAG_DOC={'2'}>} IMPORTO )),0)

                                ,

                                if([Tipo Costo]=('Dipendenti'),if(27*8*GGAFINIRE>0,27*8*GGAFINIRE,0)

                                    )

                                )                       

                        )                                        

                        )                   

                     )                  

                )

            )            

        )

)

[hic]

You have a number of naked field references in your measure (field references that are not inside an aggregation function). These will evaluate to NULL on the totals rows.

See more on https://community.qlik.com/t5/Design/Use-Aggregation-Functions/ba-p/1475833  

[Oleg_Troyansky]

Hi,

The formula is overly complex indeed. I'd recommend to look for creative ways of simplifying the logic, perhaps by improved data modeling and by using associative logic, - associating some numeric factors with various categories of data, to eliminate so many IF questions etc. If your data set is large, it will likely to be very slow.

However, the specific issue is quite easy to see - you are using "naked" field names in your IF conditions and in the calculations. These fields have unique values at the detailed level, and the formula is working. At the total level, the same fields have multiple values, and hence the condition cannot be verified and the calculation cannot be completed.

To fix it, enclose your calculation in the AGGR() function and use all of your chart dimensions as the AGGR() dimensions - this way, your "naked" fields will still have unique values within the AGGR(). For example:

sum(AGGR( <your whole formula>, Dim1, Dim2, Dim3))

This formula should work at the total level.

Cheers,