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.

Content aside

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