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
Best answer by Joseph Heun
View original
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?
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
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
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?
Sign in with your community account. This is different than your login to your GoodData account. Don't have a community login? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
