I have a dataset file, but I want to add a calculated column to the dataset, based on the max date field present in the entire dataset.
I have brought in the data, and have gotten the max(date field). Now, I want to add my calculated periodkeyfield to the dataset and don't know how to do this.
on the front end, I am able to derive this value using AddMonths() function, but I want this to be included in the script, so each Account will have a corresponding periodkeyin the final table. (not trying to sum anything up).
BlacklineFileTemp: LOAD Account, [Item Amount] as Amount, PeriodDate FROM TestBlackline.xlsx (ooxml, embedded labels);
DATE(MAX(PeriodDate)) AS MaxPeriodDate
(i have attached a copy of the dataset and QVW file). thanks!
So, all you want it to find the 6 Month Ago, 12 Month Ago, 18 Months Ago, and 24 Months Ago from your PeriodDate field for all Account? Can you have more than one Account with different PeriodDates? In this case you would want to go 6, 12, 18, 24 Month back from the max date?
hi @sunny_talwar yes. i want to find the 6 Month Ago, 12 Month Ago, 18 Months Ago, and 24 Months Ago but from the most recent PeriodDate field in the entire dataset, which is given by the Max(PeriodDate).
so for every account, there is 1 PeriodDate. and for each periodDate, I want 1 PeriodKey.
Based on the most recent PeriodDatein the entire dataset, I want to include a new column, called "PeriodKey". this will depend on if the Account's PeriodDate was between 6months ago, 12months ago, 18months ago or 24months ago, from the Most Recent Date of the entire dataset.
so for example, if my MaxPeriodDateis "02/28/2019", and i have an account with the date of "12/31/2018", (this is 3months prior to the most recent PeriodDate), so it happened within the last 6months.
this Account should now have a PeriodKey of 1. so the new column to be created in the end result dataset, should look like this:
The logic for the periodkey is in the script on the attached file
if 0-6months ago -->periodKey 1, if 7-12months ago -->periodKey 2, if 13-18months ago -->periodKey 3, if 19-24months ago --> periodKey 4
what i need help with, is how to include this calculation in the load script and create the column
Ekong, did Sunny's latest post with sample app get you what you needed? If so, please be sure to give credit by clicking the Accept as Solution button on that post...
If you still need further help, leave an update.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.