Creating a metric that shows the last previous value generated irregularly

  • 10 March 2022
  • 3 replies

Hello, I'm having trouble creating a CostsPreviousDataset metric that shows me the last previous value for the Costs metric. I managed to set the metric to show the value from the previous day (SELECT Costs FOR PREVIOUS (Date (Datasetdate))), but unfortunately the client does not generate costs once a day every day, but the generation is irregular - it can be once every three days or, conversely, four times a day etc. and the client needs to see a comparison with the previous dataset (not with previous day). Each dataset is marked with a unique integer DatasetID, but I couldn't figure out how to build the formula correctly in MAQL. Thank you for help.


Best answer by Michael Ullock 14 March 2022, 18:52

View original

3 replies

Userlevel 2

Hi Anna, 

as mentioned in our documentation here FOR Previous: By default, the FOR Previous function refers to values from one time period ago. You can customize FOR Previous by adding a second parameter to the function. But as you mention that you do not generate costs once a day, but the generation is irregular - Perhaps you will need to use a different Date Dimension in your reporting. As the "For Previous" function is essentially a static date/period defined by your metric, but you can have sales once every three days or, conversely, four times a day etc. I think the best approach here would be to use the "Week" Attribute in your metrics for example: Week (Mon-Sun)/Year.

Could you please give this a try and let us know if this helps in this case? 

Hello Michael, 

thanks for answer, anyway Week (Mon-Sun)/Year didn’t help. I tried also other options, but it is not what we are looking for. We only need to see each generated dataset and compare it to the previous dataset. I guess FOR Previous is not a right function, but there must be other possibilities, aren’t they?

Is there any easy way to compare values with values from previous datasets that are generated multiple times per day or isn’t? Can we compare datasets that are generated irregularly? I just need compare values from DatasetID = X with values from DatasetID = X-1, it cannot be that hard...


Userlevel 2

Hi Anna, 

if the Week (Mon-Sun)/Year attribute doesn't help in this case, perhaps the "Events" dataset could help you in this case. A special Event dataset can be created to contain all events related to a specific opportunity. More details on this can be found here:

Can you please review the above documentation and let us know if this could help you? Once you have implemented  the "Events" dataset you can create metrics to measure the different events between your costs. 

Hope this helps!