0

get measure monthly count for last 12 months

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.

5 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 7 mths 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
    • 7 mths ago
    • Reported - view

    • Senior Director of Product
    • Ian_Macdonald
    • 7 mths 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
    • 6 mths ago
    • Reported - view

    It is saved into the right hierarchy

    • dgf
    • 6 mths ago
    • Reported - view

    it works now after restarting Pyramid, without me changing anything

Content aside

  • Status Answered
  • 6 mths agoLast active
  • 5Replies
  • 56Views
  • 2 Following