@Ivana Gasparekova - I have a request for for support. i have a table of id | milestone_name | milestone_date. and I am looking for an average between milestones M1 and M2. How can i use MAQL to compare two dates in the same table with different milestones but the same id? I throught asking for the first one will accelerate my work downstream 🙂

Hello Maximilian, you could use a WHERE clause to define the date you are working with in a metric. Essentially, you could create two metrics finding the sum of those dates and then build the AVG metric from those.

Hi Maximilian, If you are using GoodData Cloud, a metric like this should give you average difference between two milestones - in this case “Milestone A” and “Milestone C”. It uses MIN internally so if there would be more date values for the same ID, it would use the first one.

SELECT AVG(
  SELECT DATETIME_DIFF(
    (SELECT MIN({label/milestone_date.day},{dataset/milestones}) BY {label/milestones.id} WHERE {label/milestone_name}="Milestone A"),
    (SELECT MIN({label/milestone_date.day},{dataset/milestones}) BY {label/milestones.id} WHERE {label/milestone_name}="Milestone C")
,DAY)
)

Thanks. I made it fit to my datamodel and the metric worked: SELECT AVG( SELECT DATETIME_DIFF( (SELECT MIN({label/datetime_dateformat.hour},{dataset/cube_milestones_calc}) BY {label/cube_milestones.meilenstein} WHERE {label/cube_milestones.meilenstein}="M199"), (SELECT MIN({label/datetime_dateformat.hour},{dataset/cube_milestones_calc}) BY {label/cube_milestones.meilenstein} WHERE {label/cube_milestones.meilenstein}="M100"), HOUR ) ) but i cannot use it in a visualization

{
  "title": "Bad Request",
  "status": 400,
  "detail": "A result cache error has occurred during the calculation of the result",
  "resultId": "ca93032c33e1be919b058c58da35de86a5f40edd",
  "reason": "Aggregation dimension='[attribute/cube_milestones.meilenstein/c28a880ab9ef4b069e3d3d5a0bcefc84]' is not comparable to the dimensionality='[dataset/cube_milestones_calc/c28a880ab9ef4b069e3d3d5a0bcefc84]' of the subtree",
  "traceId": "c5e77776b5e5ea29f7a8db10742c9ceb"
}

will i need to mention the iteration through the ids of the actual lines?

Is the attribute {attribute/cube_milestones.meilenstein} in the dataset {dataset/cube_milestones_calc} ? Or are these somehow two separate datasets? In my example I assumed it is all in one dataset. If not can you please share the relevant part of your model?

good catch: the table is as attached. i adjusted the metric SELECT AVG( SELECT DATETIME_DIFF( (SELECT MIN({label/datetime_dateformat.hour},{dataset/cube_milestones_calc}) BY {label/cube_milestones_calc.meilenstein} WHERE {label/cube_milestones_calc.meilenstein}="M199"), (SELECT MIN({label/datetime_dateformat.hour},{dataset/cube_milestones_calc}) BY {label/cube_milestones_calc.meilenstein} WHERE {label/cube_milestones_calc.meilenstein}="M100"), HOUR ) ) and it now does not throw an error

I think I see it now - you have there

BY meilenstein

in those inner metrics , but it should be

BY id

(fahrt_id in your case probably) (you want to be calculating the MIN date for each id)

thanks. this did the trick - and provided me the logic to go further. last question: if i have an interest into multiple milestone-jumps (eg M100-199, M199-m370, etc), it is easy to enumerate all these. but a) is it possible to show these in a single visualization or have a drop-down to select the filter the selected time?

Great to hear that. I believe you could create separate metrics for each of these jumps (just use the boundaries in the WHEREs) and then show them together in a stacked chart (you can choose to stack metrics in the configuration). That should make sense if the steps are sequential and the averages therefore reasonably additive. Or maybe you can use waterfall chart for another style of presentation. I am afraid a drop-down would be more difficult (if ypu mean tobselect which jumpbto show) as swapping metric based on some dropdown is IMO not supported. Alternatively you could potentially use the milestone attribute filter and instruct users to select two values (first and last) and let some slightly updated metric calculate the average difference, That might be flexible, but probably not so much user friendly.

Thanks!