Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

camjvine18
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)

)

Capture.PNG

1 Solution

Accepted Solutions

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

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?

camjvine18
Contributor

Re: Nested if statement and aggregation issue

Here is the qvf file.

camjvine18
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.

Re: Nested if statement and aggregation issue

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

camjvine18
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.

Re: Nested if statement and aggregation issue

So you are expecting this to be 0?

Capture.PNG

camjvine18
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.

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)

)

camjvine18
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.

Community Browser