Creating a date dimension from a dates column - comes out not sorted.

15 views
Skip to first unread message

Boris Tseytlin

unread,
May 25, 2017, 10:06:02 AM5/25/17
to Cubes
I have a table "fires" which includes a date column "date". I want to get a fires per day/month/year cube, so that the chart is a time series.
I realised I need one dimension: time. And one aggregate: record count of fires.
I followed examples and came to this model:


{
   
"name": "Firedata",
   
"dimensions":[
       
{"name": "ao"},
       
{
           
"hierarchies": [
                   
{
                       
"label": "Daily",
                       
"levels": [
                           
"year",
                           
"month",
                           
"day"
                       
],
                       
"name": "daily"
                   
}
           
],
           
"info": {
                   
"cv-datefilter": true,
                   
"cv-datefilter-hierarchy": "daily"
           
},
           
"levels": [
               
{
                   
"attributes": [ "year" ],
                   
"key": "year",
                   
"name": "year",
                   
"label": "year",
                   
"label_attribute": "year",
                   
"role": "year"
               
},
               
{
                   
"attributes": [ "month" ],
                   
"key": "month",
                   
"name": "month",
                   
"label": "month",
                   
"label_attribute": "month",
                   
"role": "month"
               
},
               
{
                   
"attributes": [ "day" ],
                   
"key": "day",
                   
"label_attribute": "day",
                   
"label": "day",
                   
"name": "day",
                   
"role": "day"
               
}
           
],
           
"label": "Date",
           
"role": "time",
           
"name":"date"
       
}
   
],


   
"cubes": [
       
{
           
"key": "fire_id",
           
"dimensions": ["date", "ao"],
           
"aggregates": [
               
{
                   
"function": "count",
                   
"label": "Fire count",
                   
"name": "record_count"
               
}
           
],
           
"name": "firedata_fire"
       
}
   
],
   
"mappings": {
     
"date.year": {"column":"date", "extract":"year"},
     
"date.month": {"column":"date", "extract":"month"},
     
"date.day": {"column":"date", "extract":"day"}
   
}
}

The problem is that the dates don't appear sorted.

Also I see that when I aggregate "Fires count per day" there is no count for days on which there were no fires (so no fire records with matching dates).

My question: is the only way to get what I want, a time series of fires on each day, is to use a table of dates? If not, how do I go about this?

Reply all
Reply to author
Forward
0 new messages