hello, i´ve an issue with creating new metric (maql in the picture), I´d like to see our current active deals (which are already counted in a metric), which are active (attribute date (product active)) from the first monday of a current month till first day of the next month. I´m always using AI, but i this case, it wasn´t very helpful. Please, could someone help me? Thanks a lot 🙂 Thread in Slack Conversation

Hello Apolena, I believe you will want to use a BETWEEN clause to count the dates between the first monday of a current month till first day of the next month. Could you please try this and let us know how you get on?

Yes, i know what you mean, i tried, does not work though

a lot of this depends on the LDM and objects used and how they connect. What is the MIN function doing here exactly? If you can send over a link to the metric, we can take a look.

Hi Apolena, I believe this is possible, you just need to slightly change your metric. If you add BY ALL OTHER to the inner metrics calculating the first and last date you are interested in, it should start working. You should add BY ALL OTHER there because with each of these inner metrics you are actually calculating a constant (representing the date) from the date dimension. And you want this constant to be comparable on any granularity. I would also recommend to add “WITHOUT PF” to make sure the inner metrics calculating first monday of this month and first day of next month are not affected by any date filters the user selects. Then I believe it should work for you. Attached is an example with the same logic (different date and metric names but otherwise similar).