Solved

Custom a DateDifff Key performance Indicator in MAQL

  • 14 August 2021
  • 7 replies
  • 82 views

  • Known Participant
  • 10 replies

Hi there everyone,

 I want to be able to average number of days between Listing Date and Today. My metric looks as follow:

 

Then I put the metric AVG of Vacant Days on dashboard and it has an error:

Is it possible to calculate directly on Gooddata instead of creating a view in DW?

Thanks a lot,

Lena

icon

Best answer by Joseph Heun 18 August 2021, 19:35

View original

7 replies

Userlevel 2

MAQL metrics are dependant on their workspace Logical Data Model. If the metric is throwing an error, we would need to look into the LDM and ensure that all objects being called are connected via the model. Furthermore, you will generally want to find Min/Max Dates for the time period in order to define the Start Date and End Date. You can then proceed to easily work with arithmetic functions based on these start and end dates. 

 

In regards to your following question: 

Is it possible to calculate directly on Gooddata instead of creating a view in DW?

Userlevel 2

The metric you have may work in your case, but since it is showing an error, I believe the “by” clause is what is causing the issue. The clause here locks the aggregation, and maybe that object is not connected to the date dimension in the LDM. I have played with metrics in my own case and can confirm that the results do work in a similar way: 

 

select AVG(THIS - Date (Ticket Created)) by Ticket Id where Organization = GoodData

 

So, this metric format can work, but it is dependant on your LDM. 

 

Also, I may have misinterpreted your previous question, Is it possible to calculate directly on Gooddata instead of creating a view in DW?

It is possible for you to make the calculations in your source data, and then upload those calculated averages to your project. 

Thank you, Joseph. It’s worked now after correcting:

My Days = SELECT AVG(select THIS - Date (Move Out Date) BY Fct Tenant Activity History).

 

But in case My Days is calculated from multiple dates, It will be not worked again.

My example: My Days =

SELECT AVG(

SELECT CASE

     when Occupancy Status = Status A then (select THIS - Date (Move Out Date)  By Fct Tenant Activity History)

    ,when Occupancy Status = Status B then (select Date(Move Out Date) - Date(Move In Date) by Fct-Accum_Prop_Status )

ELSE 0

END

 )

My LDM is as follow:

 

Thanks for your help,

Lena

Userlevel 2

In this case, you will need to use the Start/End dates for the date arithmetic. Also, when working with multiple date dimensions the best practice is to adjust your model and use Disconnected Date Dimensions

Thanks for your support, Joseph.

In GoodData multiple versions of time cannot be used together. In my case my LDM should be converted to use Event LDM as If Too Many Date Dimensions, Try Events - Gooddata Enterprise. It is difficult for me to implement it.

Thanks,

Lena

Userlevel 2

Hi Lena,

Using events like this will require you to build a separate dataset referencing the events. You can do this right in the Modeler in the the UI, but if you are encountering any specific errors please let us know and we will be happy to assist. 

If this is really more project specific for your case, you can open a direct ticket with us at Support@gooddata.com

 

That means I can create a Events View on UI without referencing to a View in Data Warehouse? 

Reply