Users may want to create a metric that displays 'NEW' when the display date is later or equal to the selected date and 'OLD' when the display date is earlier than the selected date in Strategy Developer 9.4.x-10.x. To do this, follow the steps below:
- Create a new attribute named attributeA that points to the column of the datestamp in the warehouse using the following formula:
>(((Year() * 10000) + (Month() * 100)) + DayOfMonth())
- This will display the date 1/12/2017 as 20171201.
- Create a metric named metricB that will create a sum of attributeA:
Sum()
- Create a date value prompt named promptC.
- Create a metric named metricD that converts the prompt answers from promptC to the format similar to metricB:
Sum(((Year(? ) * 10000) + (Month(? ) * 100)) + DayOfMonth(? )
- Create another metric named DateDifference that compares the value from the two metrics and sets the value to 'NEW' or 'OLD' with the following formula:
IF((MetricB >= MetricD), "NEW", "OLD")
- Create a report with the Day attribute on the column and the DateDifference metric on the row. The result is as follows for prompt answer = '1/12/2003':
