Nested if statement and aggregation issue

[Anonymous]

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)

)

[sunny_talwar]

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

[Anonymous]

Here is the qvf file.

[Anonymous]

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]

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?

[Anonymous]

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 · Accepted solution]

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]

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.