Date granularity

Some days ago a customer and I solved a little MashZone problem which I think is worth a post to share this with you.

The customer visualizes measurements that are taken continuously, normally there is one value per day. A MashApp displays the results and as the data source is updated on a regular basis the corresponding dashboard also updates.

But of course over time there will be more and more values. This leads to a common phenomenon: As the distance between now and past increases the less important are measurements of fine granularity. An aggregated view is often sufficient.

Although today you might be interested in accurate hourly stock prices it does not matter what the exact price of a given share had been on Tuesday, 25th March 2003 10:33:15 CET. The weekly or monthly average seems to be ok.

So lets do it in MashZone. Someone already prepared a sample and published it online...

Lets investigate the data feed which does such aggregation for you on the fly and dynamically. The requirement might be: Aggregate the last year's history of daily measurements.

1. There should be daily values for the last 30 day period.
2. There should be weekly values for the last four month.
3. There should be one value per two week interval for all other values in history.

This means all data feed rows must be split into three distinct subsets: the daily, weekly and biweekly period. MashZone can do this by using the copy data feed operator and placing appropriate filter conditions as indicated in the picture.

Now I will elaborate on the filter conditions that selects the three subsets in more detail.

1. There should be daily values for the last 30 day period. So the filter says Let values pass whose data column values are In or after today - 30 days. As Marko mentioned earlier there is no today in MashZone 1.0.0. But MashZone 1.0.1 contains it!
   
   
   
   So inspecting the filter condition in more detail it looks like this.
   
   
   
   As a result there is the first subset which does not need any aggregation at all. But we still need these values later.
2. There should be weekly values for the last four month. Again, this stream needs filter conditions but they are comparable to the ones before. The condition is Let values pass whose data column values are Before or in  today - 30 days and In or after today - 120 days.
   
   
   
   Fine. Now the hard part. All values that belong to the same week must be somehow collapsed.
   
   
   
   To group all rows within the selected subset that belong to one week  we first insert a numerical column and utilize another MashZone 1.0.1 feature: Enumeration. It is the little option selected which assigns each row an ascending integer value.
   
   
   
   Next we do some Math - find the remainder of the division of the ascending number by 7. This way every first day of week receives an offset of 0, every second day an offset of 1 etc.
   
   
   
   After wards the date is shifted backwards by this offset and now all measurements of a week are collapsed onto the first day of their week.
   
   
3. There should be one value per two week interval for all other values in history.
   
   The last one is a modification of one and two and the interested reader might use it as an exercise :)

Finally we use the concatenate data feed operator to create the union of all three subsets.

The entire MashApp has been attached to this post and you are free to make use of it. The MashApp looks like this:

Enjoy mashing with MashZone!