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]

I think what you need to read about is Grain Mismatch within Aggr() function and then read about NODISTINCT

Grain Mismatch is one of the Pitfalls of the Aggr function

[Anonymous]

Thanks I will read the above.

[Anonymous]

Hi Sunny, i have some odd behaviour happening when I select a subset of data for testing. Previously the code worked fine but when I only put in 2 days worth of data, I am getting Breaches where non exists as minutes are not greater than 9 and a count on a breach and an ontime of 1 when it should be one or the other. For example, Tue 16/1 4 CICS. Attached file.

[sunny_talwar]

Which expression am I looking at? Count? And what value do you expect to see for Tue 16-Jan-18 4 CICS?

[Anonymous]

Hi, the second Sheet, date by Milestone and the Count expression. I have given other examples as CICS seems to be correct today.

Also, on the first sheet the Daily Progress, the Source Breaches Daily Count should be 2 not 7.

Logic, which has been working fine on the full data load, is if Minimum Minutes are less than zero, then Breach thus count of 1. On any one day for any one Milestone it is either in Breach or Ontime.

[sunny_talwar]

I think these are snapshots from today's version of the app and I still have the old version. Won't really be able to check here 

[Anonymous]

Here you go.

[sunny_talwar]

I am sorry, but this still doesn't seem like the same file as your screenshot. Below is what I am seeing

[Anonymous]

Apologies, odd behaviour happening with different loads.

Here are the similar problems and corresponding file