Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Sum of if statements

Hello;
i am trying to sum two if statements but no results are returned.
Any idea why?
What could be changed.
the IF's on their own work very well and return the amount expected.
Summed nothing happen???
My goal is per week to sum the acitivites with problems looking at certain types of activities and a ratio of hours.
num#
(If(sum(If(ActivityType='INEN'OR ActivityType='ISSS'OR ActivityType='IDOA',1,0)
+
IF(((Sum ({<ActivityType={'FSRV'} >} _Hours_worked)+Sum ({<ActivityType= {'INEN'}>} _Hours_worked)+Sum ({<ActivityType= {'ISSS'}>} _Hours_worked)+Sum ({<ActivityType= {'IDOA'}>} _Hours_worked))/[Target Inst Hours])
>1.5,1, 

IF (((Sum ({<ActivityType={'FSRV'} >} _Hours_worked)+Sum ({<ActivityType= {'INEN'}>} _Hours_worked)+Sum ({<ActivityType= {'ISSS'}>} _Hours_worked)+Sum ({<ActivityType= {'IDOA'}>} _Hours_worked))/[Target Inst Hours])

<0.3,1,0))

>0.1,1,0)))

Hope somebody can provide some guidance.

Many thanks in advance.

Kind regards;

Kristel



2 Replies
dt_adamwilson
Contributor

Re: Sum of if statements

I'm not sure what the logic is you are aiming for but I have made it a bit more readable and have highlighted the syntax problem you are having:
it looks like you have got the sum function mixed up within the if function, possibly because of a misplaced parenthesis
hopefully you will be able to distinguish what it is you were trying to achieve

num#

(

    If(

        sum(

            If(ActivityType='INEN'OR ActivityType='ISSS'OR ActivityType='IDOA',1,0)

            +

            IF(

                (

                    (

                        Sum ({<ActivityType={'FSRV'} >} _Hours_worked) +

                        Sum ({<ActivityType= {'INEN'}>} _Hours_worked) +

                        Sum ({<ActivityType= {'ISSS'}>} _Hours_worked) +

                        Sum ({<ActivityType= {'IDOA'}>} _Hours_worked)

                    )

                    /[Target Inst Hours]

                )

                >1.5,1,

                    IF (

                        (

                            (

                                Sum ({<ActivityType={'FSRV'} >} _Hours_worked)+

                                Sum ({<ActivityType= {'INEN'}>} _Hours_worked)+

                                Sum ({<ActivityType= {'ISSS'}>} _Hours_worked)+

                                Sum ({<ActivityType= {'IDOA'}>} _Hours_worked)

                            )

                            /[Target Inst Hours]

                        )

                        <0.3,1,0

                    )

            )

        >0.1,1,0

        )

    )

)


mov
Esteemed Contributor III

Re: Sum of if statements

You can further make it more readable by replacing this:

Sum ({<ActivityType={'FSRV'} >} _Hours_worked)+
Sum ({<ActivityType= {'INEN'}>} _Hours_worked)+
Sum ({<ActivityType= {'ISSS'}>} _Hours_worked)+
Sum ({<ActivityType= {'IDOA'}>} _Hours_worked)

with this:

Sum ({<ActivityType={'FSRV','INEN','ISSS','IDOA'} >} _Hours_worked)

Community Browser