get measure monthly count for last 12 months

dgf
updated 1 yr ago
5replies
Answered

I have a [Measure] and that I want to show it's trend on a monthly basis. Therefor I created the following logic:

strtomember("[Date].[Calendar].[Date].["+ format(now()-1,"yyyy-MM-dd")+"]").parent.LAG(11) : strtomember("[Date].[Calendar].[Date].["+ format(now()-1,"yyyy-MM-dd")+"]").parent

Copy

I use it as a filter on a [Date].[Month Hie] hierarchy in order to show the measure monthly count for the last 12 months but it don't work.

Do you know why?

Thanks,

PS - when I user the measure and month hierarchy without the logic I built it shows the measure monthly trended, but from the start of time, instead of just for the last 12 months.

- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Reported - view
Hi dfg,

I recreated your expression on my cube as follows:

strtomember("[Date].[Month Dates].[Date].["+ format(now()-2764,"M/d/yyyy")+"]").parent.lag(11) : strtomember("[Date].[Month Dates].[Date].["+ format(now()-2764,"M/d/yyyy")+"]").parent

Obviously I've changed the hierarchy name to match mine, the date format to match my dates and my now()-2764 is because my cube has older dates, not up to yesterday like yours.

It works fine for me:

It works fine if I drop the list in as a combined elements filter was well.

When you test in Formulate, what shows up in the preview panel?

Ian
- dgf
- 1 yr ago
- Reported - view
- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Reported - view
dgf,

Make sure you are saving your list to the right date hierarchy and not to Measures:

Hope that helps,

Ian
- dgf
- 1 yr ago
- Reported - view
It is saved into the right hierarchy
- dgf
- 1 yr ago
- Reported - view
it works now after restarting Pyramid, without me changing anything