I uploaded our invoice data into GoodData and I would like to create a report where I will see how many days are open invoices past due. I do not have this number in my source data, I have just due date. Is it possible to calculate it?
Best answer by Martin BurianView original
It is very good question. It is possible to calculate it from the “Due Date” using date arithmetic and “THIS” macro.
To create such calculation you have to create custom Calculated Measure (also called MAQL Metric). To do it go to Manage/Metrics and click to “Create Metric” button.
Let’s say you have “Invoices” dataset with list of invoices and each of them has a “Due Date”. To calculate how many days the invoices are past due the metric will look as follows:
The “THIS” macro references the current value for the specified attribute of the Date dimension.
“By Invoice Id” defines dimensionality of the calculation. “Invoice Id” is primary key of the “Invoices” dataset.
Then I can use the Calculated Measure in the Analytic Designer (name of the Measure is “Days due”):
The reference date is today which is 2021-01-18.
So we have the calculation! What next? We can adjust format of the Calculated Measure to make it look better. It is possible to set the format when you open the Measure in the Manage section and click to “Edit” in “Metric Format” section.
The format could be for example:
After saving the format and going back to the Insight, the Insight will look as follows:
You do not want to see the invoices where still the customer has time for payment? Add filter by measure value:
With help of date arithmetic you could for example calculate average between 2 dates e.g. Due Date and Payment Date.
The THIS macro is possible to use not just with Date but Quarter, Year etc.
You find all useful documentation here:
Hope it helped.
Thank you very much. It is exactly what I needed.