We started using MapD recently and I’m trying to put together a quick demo page after experimenting some with the dashboard. Our data table is in the following (simplified) format:
Columns: - Date - Portfolio - Stock - Return
One of the charts on the page is a time series chart, displaying the sum of the Return column by the date. This is pretty straightforward to do: just define the dimension on the
"Date" and use
On the other chart however, I want to group by Portfolio but I want to display the average of the the daily total returns. In SQL terms, this would be equivalent to the following query:
SELECT Portfolio as key0, AVG(val) FROM (SELECT Portfolio, Date, SUM(Return) AS val FROM MyTable GROUP BY Portfolio, Date) GROUP by key0;
Of course I want these two charts to be crossfiltered to each other, so selecting a specific date range in the first one would filter through the second one.
I cannot figure out how to do this. Can this be done at all?
Thanks for the help in advance!