Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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)

)

View solution in original post

18 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

Here is the qvf file.

Anonymous
Not applicable
Author

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.

sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

So you are expecting this to be 0?

Capture.PNG

Anonymous
Not applicable
Author

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

sunny_talwar

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)

)

Anonymous
Not applicable
Author

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.