0

MAX Function within formula

Hi there,

I'm completely new to Pyramid Analytics so this is a fairly basic question.

What I'm aiming to achieve is a list of customers and against the list of customers I would like to show the last date they purchased a product from us (or the max purchase date).  I have been into formula builder but for the life of me I can't figure out how to get the correct result.  

Any help would be much appreciated.

Cheers

 

Paul

1 reply

null
    • Yakov_Shaul
    • 6 yrs ago
    • Reported - view

    Hi Paul,  paulsmith@yha.org.uk

    In BI Office (our former product), you could get this using custom MDX:

    WITH
SELECT
NON EMPTY
{
DISTINCT ( { [Measures].[Sales] } )
}
PROPERTIES PARENT_UNIQUE_NAME,  MEMBER_KEY, MEMBER_TYPE
 ON COLUMNS,
NON EMPTY
{
  generate({ [Customer].[state].[state].members } ,{ [Customer].[state].currentmember }*tail(nonempty({ [Date].[Date].[Date].members } ,  [Customer].[state].currentmember), 1))
}
PROPERTIES PARENT_UNIQUE_NAME,  MEMBER_KEY, MEMBER_TYPE
 ON ROWS
FROM [Pyramid Sales Demo]
CELL PROPERTIES FORMATTED_VALUE, VALUE, FONT_NAME

    Results in:
      

     

    In Pyramid 2018 it currently cannot be done.

    We will be adding “head/tail” functions to the N of N and macro filters to achieve it.

    Kind Regards,
    Yakov.

Login to reply

Content aside

  • Status Answered
  • 6 yrs agoLast active
  • 1Replies
  • 65Views
  • 2 Following