Date Diff in Detail on Grouped Report

[scott_selph]

Have a report with (5) groupings, (3) of which are suppressed, the detail section is not suppressed. I need to return the number of days between today and the max date found in the UPCDateFinish field, where Production Complete = 0. Grouping on jmpJobID and others as shown below.

The (4) fields and (3) tables I'm working with are "Jobs.jmpJobID", "JobOperations.jmoProcessID", "JobOperations.jmoProductionComplete", "UPortalClocks.UPCDateFinsh".

Example data would be:

JOBID.....ProcessID.....UPCDateFinish.....ProductionComplete (Boolean 0/-1)
123.....010.....3/1/2018.8:02am.....-1

123.....020.....3/1/2018.8:15am.....-1

123.....030.....3/1/2018.8:25am.....-1

123.....040.....(null).....0

456.....010.....3/1/2018 8:03am.....-1

456.....020.....3/1/2018 8:17am.....-1

456.....030.....(null).....0

Design View
Print Preview

[Former Member]

Should be able to create a formula

If jmoProductionComplete = 0 then

Datediff("d", today, maximum({UPCDateFinish}, {jmpJobID})

else 0

Ian

[scott_selph]

This formula does return the datediff needed. However, it also returns multiple rows of the same data, equal to the number of occurrences of the field UPDateFinish containing a date.

[Former Member]

Can't see why a formula would cause data to duplicate unless it is over riding some sort of suppression condition.

You will need to check original SQL, select statement and investigate section experts to see why data is duplicating

Ian