Hi everyone ! I'd need some help regarding the cr...
# gd-beginners
Hi everyone ! I'd need some help regarding the creation of a metric. Here are the information : • Metric A calculates the number of lost items per itemtype, using table A Metric A : SELECT COUNT(ItemId) WHERE Date ReturnDate) = (empty value) AND Date (ExpiryDate) < THIS AND Date (BorrowDate) = Metric B GETS THE NUMBER OF LOST ITEMS Metric B : SELECT MAX ((SELECT Date (BorrowDate) BY Leaseagreementid)) BY ItemId, ALL OTHER GETS THE LAST SALE FOR EACH ITEM • Table B lists the purchase price for each itemtype Desired Output : • Total value of lost items • Calculation Logic : (Number of itemtype 1 items lost * Price of Itemtype 1) + (Number of itemtype 2 items lost * Price of Itemtype 2) + (Number of itemtype X items lost * Price of Itemtype X) ATTACHED A TABLE SHOWING THE DETAIL, WITH 48€ AS THE DESIRED OUTPUT OF THE KPI Feel free to ask any question regarding the tables of metrics that could help to find the right way to build the metric. Thanks !
We are not familiar with your data or LDM directly, so it’s a bit difficult to ascertain what exactly is happening; even with metric definitions. At first glance, I would suggest running a WORKSPACE VALIDATION to check for missing values in the data. Also, calculating a value between two dates where the date has an ‘empty value’ seems to be an issue. If that validation is ok, then you may want to check for NULL values in the inner metrics where you can apply 0 values for nulls in source data. These can be based through using an IFNULL CLAUSE Lastly, I would suggest to take our free MAQL Metrics course at GoodData University, where you can learn some best practices for building metrics.