https://www.gooddata.com/ logo
Join Slack
Powered by
Hello, As an editor I would like to create a metri...
# gooddata-platform
h
Hello, As an editor I would like to create a metric based on an amount for on a date that is THIS Year To Date, so that the metric can be compared properly on the dashboard page. See screencap. Can this be achieved through current functionality?
m
Hi Hans, if you would like to create a metric that displays year-to-date, compared to the same time period the year before, it would look something like this: For this year to date: 
SELECT <Your-Metric> WHERE Year (Date) = THIS AND Date (Date) <= This
For the last years quarter to the same date: 
SELECT <Your-Metric> WHERE Year (Date) = THIS-1 AND Date (Date) <= This -365
More details on the “THIS” macro used in the metrics above can be found here: https://help.gooddata.com/doc/growth/en/dashboards-and-insights/maql-analytical-query[…]e-where-clause/referencing-floating-time-periods/this-macro/ In the part where I have added “`<Your-Metric>`” you will simply insert your metric there where you’re calculating the totals.
👍 1
m
alternatively, if you do not want to rely on deducting exactly 365 days and need to somehow handle leap yers etc. you could utilize the 
Day of Year (Date)
date attribute which is assigns to each day in any year value between D1 and D366. It is available for any date dimension and also works with THIS macro: 
SELECT <Your-Metric> WHERE Year (Date) = THIS AND Day if Year (Date) <= This
and 
SELECT <Your-Metric> WHERE Year (Date) = THIS-1 AND Day if Year (Date) <= This
h
Thanks for this clarification. Will this logic work with the comparison that is generated by the KPIs? Imagine I would put the first line of code in a metric, and choose this metric in a KPI.
m
Hi Hans, please let me test this and verify my findings. I will get back to you once I have more details on how this works with the KPI’s 🔍
h
Thanks Michael. Pls don't lose sleep over this ;-)
👍 1
m
Hi Hans, I was running some tests when you only add the first metric in a KPI, and then use the comparison functionality that is generated by the KPIs, this unfortunately only works with some of the available time periods in the drop-down menu. This doesn’t work with them all, because of how it’s being calculated on the backend. So in this case, you’ll need to use both the metrics that we provided to get this to work consistently
h
Can you make it something your product team can look at? When you sell software to product companies (like ours) to show metrics, the likelihood of those metrics being compared on a YTD basis is pretty high. 😉 I would like instead of a SUM() metric a SUMYTD() metric to be there. I think it can be recovered also for your cloud platform? SO it wouldn't be a lost investment.
i
Hey @Hans Cornette, Thanks a lot for sharing your use case with us, really appreciated! I agree with you and this might be a useful feature for both Platform and Cloud users. 😉 Therefore I’ve gone ahead and share your idea with our Product team! Thank you again for your feedback.
t
Hi, @Hans Cornette, I might be wrong, but in case your would be willing to switch from KPIs to Headlines, then: • Analytical designer offers time over time comparison. E.g., filter the metric to this year and then add “SP year ago” comparison. • Or in case you need more flexibility, with headlines you can define a custom secondary metric Pardon me if I didn’t properly understood your need.
h
Hi Tomas, thanks I will look into that. This can probably help us without too much changes.
Hi @Tomas Muchka, We compared a numeric field in this Headlines widget, but it doesn't work as we thought it would. It doesn't compare the YTD when we choose This year VS same period last year. For example: we'd like to see a headline that shows gross amount 23 vs 24 YTD. Specifically: 01.JAN - 13.FEB 2024 VS the same period last year 1/1-13/2 2023. What is seems to do is compare ytd (01/01-13/02) 2024 with full year (01/01 - 31/12) 2023, when we select the Compare VS Same Period last year. Can you help?
t
Hi Hans, I think you would need to define a custom metric as my colleagues suggested at the beginning of this thread. Unlike KPIs, headlines allow a custom comparison/secondary metric.
h
Okay, I see thx
Open in Slack
PreviousNext
Powered by