In an Invoicing table, I have a Quantity field and in the script I would like to have a field with the quantity Y (current year) and another field the quantity Y-1 from the previous year.
I knwo how it works in set Analysis, but I need both fields in the script to make a calculation.
Hi, if there is not too much data, you can load again the invoicing table adding a year to the date and loading the quatity as the new field, something like:
Concatenate (InvoiceTable)
LOAD
..., // Fields that doesn't change
Date(AddYears(InvoiceDate,1)) as InvoiceDate
Quantity as QuantityLY
Resident InvoiceTable;
Note that this will create new rows, so some rows will have value on Quantity field and others on the QuantityLY field, but none of the rows will have values on both fields.
If you want to make calculations maybe you want to load again using a group by to sum the values with the needed granularity.
@Amina Load current year data first then last year data and join them with Key field so that you will get two columns for same IDs CY_Quantity and LY_Quantity or If you want row wise you can concatenate both loads for CY and LY using flags.
