Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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.
misplaced brackets
Sum({<[MonthYear]={"$(=Max([MonthYear]))"}>} [Stock Price])
Sum({<[MonthYear]={"$(=Max([MonthYear],2))"}>} [Stock Price])