0

Previous Month Dimension Value

Hello,

This has to be simple and I am sure I am missing something  very simple

I have an attribute for the current month lets say account_status ('ACT','CLS', etc)

I just want to display the previous month account status and if there is a difference apply business logic

I do this a whole lot with measures, works great

In this case I want to return

Filter set to current month

Account AccountStatus PriorMthAccountStatus (in essence a lag(1) but when I use lag I get the balance and not the value ACT)

1            CLS                 ACT

Thanks for your help

Mad

2 replies

null
    • VP Product Management
    • Ian_Macdonald
    • 2 wk ago
    • Reported - view

    Hi 

    You have to remember that Pyramid Models are Dimensional in nature. Thus each unique value in a column in the underlying table makes up the Members of Hierarchy based on that column. Each hierarchy is treated as an independent entity. In semantic calculations, all relationships between columns in a row are lost. As such, Month and Acct Status are independent of each other. There is no such thing in the model as last month's acct status. I suspect in your data, there is only ever one status for any given month, hence in your grid if you put Month and Acct Status on your rows, you see a one to one correspondence, but this is just an artifact of your data values.

    As you point out, it works great for Measures, as those are aggregated for each Member in your hierarchy, so if I point to the previous Member, you get that Member's aggregate.

    The trick here is use Text aggregation on your Acct Status column. It then becomes a Measure and your Previous or Lag(1) functions will work perfectly.

    Here's some data loaded into my model.

    Set Acct Status Measure aggregation to Text. You can hide the attribute or not, up to you.

    Create a Measure using the Acct Status Measure and Previous Member for Month.

    And there you go!

    Hope that helps.

    Ian

    • Mad_Amruthur
    • 2 wk ago
    • Reported - view

    Thanks for the detailed explanation, Ian

    It helps me understand the concept in Pyramid that I was missing.

    Since this is so easy and common in plain SQL, I was trying to figure out how to achieve the same in Pyramid..

    Thanks again, will try the text aggregate 

    Mad

Login to reply

Content aside

  • Status Answered
  • 2 wk agoLast active
  • 2Replies
  • 29Views
  • 2 Following