# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## Nested if statement and aggregation issue

Below formula will not make the date on Wed 11-Oct under Ontime a 0, is still comes up as a 1. It works fine for the next line Tue 10-Oct.

sum(

aggr

(

If(

min(Minutes)<0,1,

If

((sum(aggr(If(min(Minutes)<0,1),Category,Milestone,Date))=0

AND max(Minutes)>0),1,0)

),

Status,Category,Milestone,Date)

)

1 Solution

Accepted Solutions
MVP

## Re: Nested if statement and aggregation issue

Check this

Sum(

Aggr

(

If(

Min(Minutes)<0,1,

If

((Sum(Aggr(NODISTINCT If(min(Minutes)<0,1),Category,Milestone,Date))=0

AND max(Minutes)>0),1,0)

),

Status,Category,Milestone,Date)

)

18 Replies
MVP

## Re: Nested if statement and aggregation issue

Expression and image may not be enough to understand what is going on... would you be able to share a sample?

Contributor

## Re: Nested if statement and aggregation issue

Here is the qvf file.

Contributor

## Re: Nested if statement and aggregation issue

The point of the expression is that within the Category, the Status for the Day under question is either a Breach or Ontime, it cannot be both.

MVP

## Re: Nested if statement and aggregation issue

Which tab do you have the chart from the image above?

Contributor

## Re: Nested if statement and aggregation issue

Updated this old file (original at work) to get the same issue. Refer Milestone by Day tab.

MVP

## Re: Nested if statement and aggregation issue

So you are expecting this to be 0?

Contributor

## Re: Nested if statement and aggregation issue

No, if you scroll down to sub heading labelled 11 WSS SE & Midanz Start and choose 11/10 that is where the issue resides.

MVP

## Re: Nested if statement and aggregation issue

Check this

Sum(

Aggr

(

If(

Min(Minutes)<0,1,

If

((Sum(Aggr(NODISTINCT If(min(Minutes)<0,1),Category,Milestone,Date))=0

AND max(Minutes)>0),1,0)

),

Status,Category,Milestone,Date)

)

Contributor

## Re: Nested if statement and aggregation issue

You legend, I spent hours on this, NODISTINCT was all that was missing. Will double check tmrw on my full dataset but appears to the trick and read up more on NODISTINCT.