Skip to Content
0
Former Member
Feb 08, 2012 at 11:33 PM

Formula variable - Maximum Date in result output

23 Views

Hello Guys,

Scenario -

Data in Cube

*Reseller 	Sales Date*
RS1		01.01.2010
RS1		01.01.2011
RS1		01.01.2012
RS2		01.01.2008
RS2		01.01.2009
RS2		01.01.2010

Required Output - Display one record for each Reseller with recent Date.

*Reseller 	Sales Date*
RS1		01.01.2012
RS2		01.01.2010

What I did till now --->

Created a Formula and included a Formula Variable (replacement Path ref char - Sales Date) hence I got the date as keyfigure.

Reseller 	Sales Date	Sales Date Formula
*RS1		01.01.2010	201,00,101*
*RS1		01.01.2011	201,10,101*
*RS1		01.01.2012	201,20,101*
*RS2		01.01.2008	200,80,101*
*RS2		01.01.2009	200,90,101*
*RS2		01.01.2010	201,00,101*

For the Sales Date Formula (Calculation tab - Calculate result as Maximum) to display the result as maximum.

The issue is that when I remove the Sales Date from Drilldown the Sales Date Formula display X. I was expecting the result row to be displayed i.e the maximum value for RS1 and RS2.

Can you guide where I am going wrong... or any other method to achieve the above output is appreciated.

-Amit