Using filters the expression represents well the the sum of the lines, without filters it does not considere all data

[Anonymous]

Hi Team,

Using filters the expression represents well the the sum of the lines, without filters it does not considere all data.

I mean, I have an expression to calculate the value of "compras" wich on ly shows values if we select one filter or if we include the column Code in the table. If we don't include Code in the table it doesn´t make the sum of the lines and shows zero.

I developed two scripts wich are below but none of them worked.

Can someone help me?

Thank you.
Catarina Brito

I already tried both script below but

//************************************************************************************************************************* QlikTech :

//

//=         IF(

//            (Code= '101' OR Code= '102' OR Code= '161' OR Code = '162')

//            AND SHKZG = 'H'

//            AND Trim(KZZUG) = '' "

//            , sum(Qtd*(-1)),

//       

//                        IF(

//                            (Code= '101' OR Code= '102' OR Code= '161' OR Code= '162')

//                            AND SHKZG = 'S'

//                            AND Trim(KZZUG) = ''

//                            ,sum(Qtd),    0    )

//         )   

//************************************************************************************************************************* SET ANALYSIS:     

=(SUM({<Code= {"101", "102", "161", "162"},

                SHKZG = {"H"},

                KZZUG = {""} >}

                Qtd)*-1)   

           +

        (SUM({<Code= {"101", "102", "161", "162"},

                SHKZG = {"S"},

                KZZUG = {""} >}

                Qtd))

         +

        IF(    ((Code= '101' OR Code= '102' OR Code= '161' OR Code= '162') AND SHKZG = 'H' AND Trim(KZZUG) = '')

            OR

            ((Code= '101' OR Code= '102' OR Code= '161' OR Code= '162') AND SHKZG = 'S' AND Trim(KZZUG) = '')

            ,Qtd, 0)

[stigchel]

The problem is with the If function, without the Code as dimension the if is not evaluating correctly.

Please read:

Use Aggregation Functions!

Conditional Aggregations

[Anonymous]

Hi,

First of all thank you for the answer.
Someone else already told me that I have to use aggregation functions, however I wasn't able to do it yet.
the problem persists.

Can you please tell me how exactly shall I do in my specific case, using the if conditions that I mentioned previously in the discussion?

*************************************************************************************                  
what I have tried...

=sum(aggr(

                (SUM({<Code= {"101", "102", "161", "162"},

                        SHKZG = {"H"},

                        KZZUG = {""} >}

                        Qtd)*-1)

                   +

                (SUM({<Code= {"101", "102", "161", "162"},

                        SHKZG = {"S"},

                        KZZUG = {""} >}

                        Qtd))

        , Code))

                 +

                IF(    ((Code= '101' OR Code= '102' OR Code= '161' OR Code= '162') AND SHKZG = 'H' AND Trim(KZZUG) = '')

                    OR

                    ((Code= '101' OR Code= '102' OR Code= '161' OR Code= '162') AND SHKZG = 'S' AND Trim(KZZUG) = '')

                    ,sum(aggr(sum(Qtd), Code)), 0)

*************************************************************************************                   

Thanks

Catarina

[stigchel]

Use set analysis for the last part as well

+

sum(aggr(sum({<Code={ '101','102','161','162'},SHKZG={'H','S'},KZZUG={''}>} Qtd),Code))