Aggregation of fiscal year to current month in load script

[xavierwiltbank]

Hello all! I could use some help with a load script.

What I am looking to do is this: I have a table with rows id's, fiscal years, dates, and amounts. I have the table sorted like so:

ID	Fiscal year	Date	Amount
1	2019	July 1st, 2018	5
1	2019	Aug 1st, 2018	10
1	2019	Sep 1st, 2018	100
2	2019	July 1st, 2018	1

I want to create a 5th field, Fiscal Year to Date Amount (FYTD Total), which sums up the current and all previous months, that would have the following values.

ID	FYTD Total
1	5
1	15
1	115
2	1

Here is what I am currently doing.

Load
    "Date",
    "Fiscal Year",
    "ID",
    "Date", 
    if("ID" = Previous("ID"), Previous("FYTD Total") + "Amount", "Amount") as "FYTD Total";
Resident "TempFact" Order By "ID", "Date";

I've attempted to set the FYTD total to 0 prior to this load script so that it is not null.

I've tried various attempts at using preceding loads, to no avail. Any help here would be much appreciated. I may be missing an obvious solution, as this seems like a pretty common use case.

Thanks!

[sunny_talwar · Accepted solution]

Try this

LOAD  "Fiscal Year",

    "ID",

    "Date",

    "Amount",

    If("ID" = Previous("ID") and [Fiscal Year] = Previous([Fiscal Year]), RangeSum(Peek("FYTD Total"), "Amount"), "Amount") as "FYTD Total";

Resident "TempFact"

Order By "ID", "Date";

[xavierwiltbank]

Amazing, thanks Sunny! To sum up, I think that my mistakes were not having the Fiscal Year = Previous Fiscal Year check correct, and not using the RangeSum function. I'll have to take a closer look at RangeSum.

Thanks!

~Xavier