Running Totals based on different dates

[#1]

I am trying to create a straight table that contains a running total of projects that were planned to complete vs. a running total of projects that actually completed by month.  I am able to present the data I want in two separate straight tables but would prefer to have them in the same table and have a variance column.

I am simply using a Month function for Original Plan End Date for the first table (on left) and then using the following formula for the second table (on right) (=If( Not IsNull(FieldworkActualEndDate),Month(FieldworkActualEndDate),If(Not IsNull(FieldworkEstimatedEndDate),Month(FieldworkEstimatedEndDate),Month(ScheduleEndDate))) )  Both tables use the simple (Count([Project ID]) for the expression and are both full accumulation.  I would like something like the following:

Any help you can provide would be greatly appreciated,

Chad

[miguelbraga]

Hey there:

See this attached QVW.

Regards,

MB

[#3]

Hi Miguel,

Thanks for the reply.  I don't currently have totals by month in my load statement.  I was hoping there would be a way to use either set analysis or a Count(If statement) to produce the same result.  I currently have a static original plan spreadsheet that I load that contains a date projects are suppose to end.  Then I have an additional load that brings in actual dates and estimated dates of when projects actually end or are currently estimated to end.  Can you think of any way to do this using a formula or is it best just to load this summary info from the start?

Thanks,

Chad

[johnw]

The alt() function should simplify the date expression in the second table:

alt(FieldworkActualEndDate,FieldworkEstimatedEndDate,ScheduleEndDate)

But I assume these three dates are on the same table. If so, I'd load that date in as a field, so something like this:

alt(FieldworkActualEndDate,FieldworkEstimatedEndDate,ScheduleEndDate) as ActualAuditEndDate

So now we at least have two date fields instead of a date field and a complicated calculated dimension. From here, you can create a canonical date calendar combined with a little set analysis to accomplish what you're looking for.

Canonical Date

Or if your data set is small enough, you could get away with using a date island:

[Months]:
LOAD month(date(makedate(2016,recno()))) as [Month]
AUTOGENERATE 12;

And build your chart like this:

Audit End Date   = [Month]
Total Plan End   = count(if(month([OriginalPlanEndDate])=[Month],[Project ID]))
Total Actual End = count(if(month([ActualAuditEndDate] )=[Month],[Project ID]))
Variance         = [Total Plan End] - [Total Actual End]

But the count(if()) will make the performance terrible if your data set is large. I'd probably use a canonical date regardless of data size, but this is arguably simpler. You can do the count if with your calculated dimension too, or with the simplified alt expression above, but I'd go ahead and create the field in script if practical.

[johnw · Accepted solution]

I'll go ahead and expand on the canonical date thing. I think it would look something like this for your case, assuming you already followed my advice to add an ActualAuditEndDate to the table.

[Date Bridge]:
LOAD
[Project ID]
,[OriginalPlanEndDate] as [Date]
,'Plan End' as [Date Type]
RESIDENT [Your Data]
;
CONCATENATE ([Date Bridge])
LOAD
[Project ID]
,[ActualAuditEndDate] as [Date]
,'Actual End' as [Date Type]
RESIDENT [Your Data]
;
[Canonical Calendar]:
LOAD *
,month([Date]) as [Month]
;
LOAD date(fieldvalue('Date',recno())) as [Date]
AUTOGENERATE fieldvaluecount('Date')
;

And the chart would be:

Audit End Date   = [Month]
Total Plan End   = count({<[Date Type]={'Plan End'}>}   distinct [Project ID])
Total Actual End = count({<[Date Type]={'Actual End'}>} distinct [Project ID])
Variance         = [Total Plan End] - [Total Actual End]

[#6]

John,

Thanks so much, this was exactly what I was looking for.

Chad