To GoodData homepage
Solved

Query two rows with arbitrary ids

  • 20 December 2023
  • 3 replies
  • 36 views
erik-planera

Hello -

I am trying to figure out if it is possible in an Insight to do math against two arbitrary rows in a dataset. Say for instance I have a “projects” dataset and I want to do a time diff of project create time(date) against two projects selectable by a user. We would need a dropdown to pick two different ids from a list to compare against. We can’t use a filter with two selections in, because the math would be backwards depending on order (if it was even possible to do it).

I could write a SQL query to do this, but it requires two variables to be passed in.

Thanks

icon

Best answer by Moises Morales 20 December 2023, 22:27

View original

3 replies

Moises Morales
Rank
Userlevel 3

Hi Erik,

 

Using variables in the platform queries is not possible. You could create a standard query that would calculate the time diff for all projects, then add it to a dashboard via an insight and use a dashboard filter for the project attribute so the user can select either and then the insight will show the corresponding value. More details about attribute filters can be found in our documentation here: https://www.gooddata.com/docs/cloud/create-dashboards/attribute-filters-dashboards/. 

 

-Moises

erik-planera

Hi Moises, thanks for the response. I “think” I might be able to make that work.

Do you have a suggestion for how to do that calculation? I can’t find a way to do time diff over the whole table. Or, would I need to convert to a numeric?

I have tried looking into FIRST/LAST_VALUE, but they only take numerics, and these fields are dates.

 

Moises Morales
Rank
Userlevel 3

You’re welcome. Date time diff is documented here: https://www.gooddata.com/docs/cloud/create-metrics/maql/time-arithmetics/datetime-diff/, but please note that the function only works with timestamp or date columns. In this case, I believe you should have to work with facts instead so you can calculate the difference between two periods of time and then slice it by an attribute, for example, “Project”. The filter in the dashboard should work as expected.

 

Please note that I am not familiar with your data, but in general, this is how it works. 

I would recommend checking the following resource to get familiar with MAQL : https://university.gooddata.com/getting-started-with-maql. 

 

-Moises

 

 

Reply


Join us on
Welcome to the GoodData Community!Cookie settings