cancel
Showing results for 
Search instead for 
Did you mean: 

Date Diff in Detail on Grouped Report

scott_selph
Participant
0 Kudos
124

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Should be able to create a formula

If jmoProductionComplete = 0 then

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

else 0

Ian

scott_selph
Participant
0 Kudos

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
0 Kudos

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