Awesome, thank you. Your script idea should certainly get me on the right track I think. I hadnt used the Group by clause before. I managed to get a result with a few minor syntax changes. I don't get the same answer, as I get from my expression clause but I suspect thats possibly because the my underlying data groups might be a bit trickier than my post reveals since there are date fields etc as well. The value I'm guetting at present from the below code seems like its adding up all 5M records for that Operator, wheras I want it per production Date and if I add GROUP BY Operator, Date; the values get much smaller but still incorrect.
I'll work on it a bit more and post if I find a resolution.
sum(totalTime)+sum(overTime)-sum(absentTime) - sum(if(lostTimeId='AB2' or lostTimeId='AT' or lostTimeId='CR' or lostTimeId='ME' or lostTimeId='NS' or lostTimeId='NT' or lostTimeId='NW' or lostTimeId='RE' or lostTimeId='TR', duration,0)) as OnStdTime
RESIDENT [REOperationDayHistory] GROUP BY Operator;
I'm not coming right with this, even with the suggestions received. Please can someone help me resolve.
Attached is a QVD of part of my data and the QVW (simplified to just include this issue and I've simplified it further to just the ClockTime calculation)
My problem is that while the set expression calculation works for individual dates on the Pivot table, when multiple dates are selected it shows a blank.
I would like the ClockTime shown for each day in the selection, so if I choose dates of 01 June - 05 June then I want to see the ClockTimes for those days displayed.
To explain the QVD data loaded, I have daily (CalendarDate) production activities (operationId) done by Operators (Operator) and those operators are grouped into a production line (workCenter).
Not shown in the data but it is in there somewhere is that operationIds aggregate the next lower level of detail called barcode - which are individual scans of parcels of work done. In the pivot I am skipping over this level of detail.
Ideally I'd like a drill down ability from CalendarDate -> workCenter -> Operator -> operationId with the data aggregated at each level.
However, aggregation at the CalendarDate -> workCenter level is the minimum requirement.
If one selects multiple dates then I want to see the total for the selection.
Drilldown works for a single date selection. e.g. 4 June. Everything seems correct except for the ScriptClockTime calculation. i.e. it should also show 660 in most cases in this example data.
If I close the Pivot levels the total for AggrClockTime is correct but the ScriptClockTime calc is wrong.
If I now select 2 consecutive dates instead of seeing data for each date, only the first date shows anything but if I chose either 04/06/2012 or 05/06/2012 separately I would get the correct answer for AggrClockTime.
If I choose 3 consecutive dates everything shows me 0.
Clearly an issue with how I'm aggregating. Ideally I'd like to do this aggregation (or at least most of it) in the LOAD script, but if I can't then how can I fix my Aggr formula in the pivot table.
Code is attached, but effectively I have this expression for AggrClockTime in the pivot table: