1

YTD Formula in PQL

Hello everyone,
I would like to create a formula (PQL) in an InMemory model that allows me to display the cumulative booking amount as Year to Date( YTD).

The attribute I would like to use for the months is called ‘[KJ Month in Year]’.

My first approach was to use a parameter for all ‘[KJ Month in Year]’ members

 

 I then inserted this into a list using the Month to Date function. 

 In the last step, I then insert the list into an aggregation to obtain only the cumulative value.

 However, at the end I receive an error message.

 

Does anyone have an alternative approach in mind that works?

Thanks and best regards, Jona

2 replies

null
    • VP Product Management
    • Ian_Macdonald
    • yesterday
    • Reported - view

    Hi 

    All of the "to date" function blocks need to work on  a DATE, not a month name, week number or anything like that. So first off, create a parameter based on a Date hierarchy. Then use the YTD function block, using the Date parameter, then aggregate the  results to give you YTD.\

    Hope that helps.

    IAn

    • Customer Solutions Architect
    • Moshe_Yossef
    • 16 hrs ago
    • Reported - view

     you can also do this with the [KJ Month in Year] parameter, you just need to aggregate a range between January (or the null member- not sure what it represents) and the parameter.

    in PQL that would be:

    Aggregate(
  Range(
  [Kalendar].[KJ Month in Year].[Januar],
  [Kalendar].[KJ Month in Year].@[parameter id]
     )
)

    This way - you are aggregating on all the months until the selected one, which is essence makes it a YTD element.

    Make sure the context is set for table Kalendar and column KJ Month in Year.

    I hope this helps,

    Moshe

Login to reply

Content aside

  • 1 Likes
  • 16 hrs agoLast active
  • 2Replies
  • 12Views
  • 3 Following