Evaluate Max(..) Expression in Set Analysis

[JustinDallas]

Hello Folks,

I'm working with some set analysis, and I can't get it to play right.  What I would like to say is "Show me the sum where MonthYear=Max(MonthYear,2)"

Here is my dummy data:

[Test Data]:
Load *,
[Stock Date] As '%calendar_date_key'
;
Load *,
Date#([Stock Date Text], 'MM/DD/YYYY') As 'Stock Date'
;
Load * Inline
[
'Company', 'Stock Date Text', 'Stock Price'
'AAPL', '08/26/2023',	194.50
'AAPL', '07/15/2023',	193.62
'AAPL', '06/04/2023',	192.75
'AMD', '08/26/2023',	110.09
'AMD', '07/15/2023',	113.00
'AMD', '06/04/2023', 	110.61
]
;


Drop Field [Stock Date Text]
;

MasterCalendar: 
Left Keep([Test Data])
Load 
 TempDate AS '%calendar_date_key', 
 DayStart(TempDate) as CalDate,
 Dual(Month(TempDate) & ' ' & Year(TempDate), 
  	MonthStart(TempDate)) As 'MonthYear'
;

//=== Generate a temp table of dates === 
LOAD 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 MIN(FieldValue('%calendar_date_key', recno()))-1 as mindate,
 MAX(FieldValue('%calendar_date_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%calendar_date_key');

Exit Script
;

Here is the data table that I would like to see:

Company	First Max Date (08/26/2023)	Second Max Date(07/15/2023)
AAPL	194.50	193.62
AMD	110.09	113.00

I've tried various variations of the following query to no avail

Sum({<[MonthYear]={"=$=(Max([MonthYear]))"}>} [Stock Price])

Sum({<[MonthYear]={"=$=(Max([MonthYear],2))"}>} [Stock Price])​

Any help is greatly appreciated.

[cristianj23a]

Hi JustinDallas, try this code:

[Test Data]:
LOAD *,
Date#([Stock Date Text], 'MM/DD/YYYY') as [Stock Date]
Inline [
'Company', 'Stock Date Text', 'Stock Price'
'AAPL', '08/26/2023', 194.50
'AAPL', '07/15/2023', 193.62
'AAPL', '06/04/2023', 192.75
'AMD', '08/26/2023', 110.09
'AMD', '07/15/2023', 113.00
'AMD', '06/04/2023', 110.61
];

Next, we create a master calendar:

MasterCalendar:
LOAD
TempDate as [Stock Date],
MonthEnd(TempDate) as MonthYear
;
LOAD
Date(MinDate + IterNo() - 1) as TempDate
WHILE MinDate + IterNo() - 1 <= MaxDate
;
LOAD
Min([Stock Date]) as MinDate,
Max([Stock Date]) as MaxDate
Resident [Test Data];

You can now use the FirstSortedValue() function to get the stock price for the two high dates for each company in a table object:

• Dimension: Company
• Expressions:
  • First Max Date: FirstSortedValue([Stock Price], -[Stock Date])
  • Second Max Date: FirstSortedValue([Stock Price], -Aggr(NthField(FieldValueList(-[Stock Date]), 2), Company))

Please note that FirstSortedValue([Stock Price], -[Stock Date]) returns the value of [Stock Price] for the maximum date (the most recent date since we are using a minus in front of [Stock Date]) . FirstSortedValue([Stock Price], -Aggr(NthField(FieldValueList(-[Stock Date]), 2), Company)) returns the value of [Stock Price] for the second maximum date.

It's important to note that this approach only works if you don't have more than one record for the same company on the same date. If you have, you need to add more logic to handle these cases.

Regarts.

[vinieme12]

misplaced brackets

Sum({<[MonthYear]={"$(=Max([MonthYear]))"}>} [Stock Price])

Sum({<[MonthYear]={"$(=Max([MonthYear],2))"}>} [Stock Price])