Saturday, 21 January 2017

Aggregating and propagating field values across asynchronous sources

That was a title full of made up jargon, not even standard database/tableau jargon! Hopefully an example will make it clear.

First of all, what do I mean by asynchronous sources data? It is the sort of data that comes from multiple sources, not at the same times (often from separate files/tables, after a union). A screenshot as usual is worth a thousand words.
 In this example let's assume we have a ship that reports its GPS coordinates every half hour, and the energy consumption on board every minute (it's fitted with a smart meter!). But what if we want to come up with energy usage for each position? The easy solution is to come up with a calculated 'half hour time' that truncates the original timestamps to half hour
left([Time],3)+if int(mid([Time],4,1))<3 then '00' else '30' end
 throw that on the level of detail, the x and y on rows and columns, and sum(E) will do the right thing. All well and good. There's the issue of truncating rather than rounding the time, and not interpolating intermediate positions but let's assume that level of accuracy is not important for this.

But what if the position is not consistently every half hour, what if it is less often when it moves slow, and more often when it moves fast? Then, assuming we still don't need to interpolate intermediate positions, we can avoid aggregation and instead try and fill in the null Xs and Ys using table calculations:

This is a more robust method for non-regular interval reports, we can even copy paste it into a new sheet as a clipboard source if the table calculations are an issue.

No comments:

Post a Comment