Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'd like to calculate the cumulative multiplication..
Example:
Date, Product, Amount
2019-01-01, ProductA, 0.2
.
.
2019-01-31, ProductA, 0.5
The calculated field for Product A in Jan 2019 will be 0.2 * .... * 0.5
May I know how can we achieve this? Thank you.
Try this:
temp:
LOAD *,
AUTONUMBER(Date & '|' & Product) as ID.#key;
LOAD * INLINE [
Date,Product,Amount
01.01.2019,ProductA,0.2
09.01.2019,ProductA,10
31.01.2019,ProductA,0.5
01.01.2019,ProductB,0.33
14.01.2019,ProductB,10
31.01.2019,ProductB,2
];
//If you need the data for every date in the interval:
tempMissingDates:
LOAD
DATE(MIN(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MinDate,
DATE(MAX(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MaxDate
AUTOGENERATE FIELDVALUECOUNT('Date');
tempMissingDates2:
LOAD
DATE(MinDate +ITERNO()-1,'DD.MM.YYYY') AS Date
RESIDENT tempMissingDates
WHILE MinDate +ITERNO()-1 <= MaxDate;
DROP TABLE tempMissingDates;
//In case you have multiple products:
tempProducts:
LOAD DISTINCT Product RESIDENT temp;
LEFT JOIN (tempMissingDates2) LOAD * RESIDENT tempProducts; DROP TABLE tempProducts;
CONCATENATE (temp)
LOAD
Date,
Product,
[ID2.#key] AS [ID.#key],
1 AS Amount
WHERE NOT EXISTS ([ID.#key],[ID2.#key]);
LOAD
Date,
Product,
AUTONUMBER(Date & '|' & Product) as ID2.#key
RESIDENT tempMissingDates2;
DROP TABLE tempMissingDates2;
temp2:
LOAD *,
ROWNO() AS Row;
LOAD *,
Amount as AmountMultipliedCumulative
RESIDENT temp
ORDER BY Product ASC,Date ASC;
DROP TABLE temp;
temp3:
LOAD
Date,
Product,
Amount,
IF(PREVIOUS(Product)=Product,RANGESUM(PEEK(AmountMultipliedCumulative)*AmountMultipliedCumulative),AmountMultipliedCumulative) AS AmountMultipliedCumulative
RESIDENT temp2;
DROP TABLE temp2;
Weird, already posted once, but the post somehow dissapeared. Anyway, try this:
temp:
LOAD *,
AUTONUMBER(Date & '|' & Product) as ID.#key;
LOAD * INLINE [
Date,Product,Amount
01.01.2019,ProductA,0.2
09.01.2019,ProductA,10
31.01.2019,ProductA,0.5
01.01.2019,ProductB,0.33
14.01.2019,ProductB,10
31.01.2019,ProductB,2
];
//If you need the data for every date in the interval:
tempMissingDates:
LOAD
DATE(MIN(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MinDate,
DATE(MAX(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MaxDate
AUTOGENERATE FIELDVALUECOUNT('Date');
tempMissingDates2:
LOAD
DATE(MinDate +ITERNO()-1,'DD.MM.YYYY') AS Date
RESIDENT tempMissingDates
WHILE MinDate +ITERNO()-1 <= MaxDate;
DROP TABLE tempMissingDates;
//In case you have multiple products:
tempProducts:
LOAD DISTINCT Product RESIDENT temp;
LEFT JOIN (tempMissingDates2) LOAD * RESIDENT tempProducts; DROP TABLE tempProducts;
CONCATENATE (temp)
LOAD
Date,
Product,
[ID2.#key] AS [ID.#key],
1 AS Amount
WHERE NOT EXISTS ([ID.#key],[ID2.#key]);
LOAD
Date,
Product,
AUTONUMBER(Date & '|' & Product) as ID2.#key
RESIDENT tempMissingDates2;
DROP TABLE tempMissingDates2;
temp2:
LOAD *,
ROWNO() AS Row;
LOAD *,
Amount as AmountMultipliedCumulative
RESIDENT temp
ORDER BY Product ASC,Date ASC;
DROP TABLE temp;
temp3:
LOAD
Date,
Product,
Amount,
IF(PREVIOUS(Product)=Product,RANGESUM(PEEK(AmountMultipliedCumulative)*AmountMultipliedCumulative),AmountMultipliedCumulative) AS AmountMultipliedCumulative
RESIDENT temp2;
DROP TABLE temp2;
Try this:
temp:
LOAD *,
AUTONUMBER(Date & '|' & Product) as ID.#key;
LOAD * INLINE [
Date,Product,Amount
01.01.2019,ProductA,0.2
09.01.2019,ProductA,10
31.01.2019,ProductA,0.5
01.01.2019,ProductB,0.33
14.01.2019,ProductB,10
31.01.2019,ProductB,2
];
//If you need the data for every date in the interval:
tempMissingDates:
LOAD
DATE(MIN(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MinDate,
DATE(MAX(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MaxDate
AUTOGENERATE FIELDVALUECOUNT('Date');
tempMissingDates2:
LOAD
DATE(MinDate +ITERNO()-1,'DD.MM.YYYY') AS Date
RESIDENT tempMissingDates
WHILE MinDate +ITERNO()-1 <= MaxDate;
DROP TABLE tempMissingDates;
//In case you have multiple products:
tempProducts:
LOAD DISTINCT Product RESIDENT temp;
LEFT JOIN (tempMissingDates2) LOAD * RESIDENT tempProducts; DROP TABLE tempProducts;
CONCATENATE (temp)
LOAD
Date,
Product,
[ID2.#key] AS [ID.#key],
1 AS Amount
WHERE NOT EXISTS ([ID.#key],[ID2.#key]);
LOAD
Date,
Product,
AUTONUMBER(Date & '|' & Product) as ID2.#key
RESIDENT tempMissingDates2;
DROP TABLE tempMissingDates2;
temp2:
LOAD *,
Amount as AmountMultipliedCumulative
RESIDENT temp
ORDER BY Product ASC,Date ASC;
DROP TABLE temp;
temp3:
LOAD
Date,
Product,
Amount,
IF(PREVIOUS(Product)=Product,RANGESUM(PEEK(AmountMultipliedCumulative)*AmountMultipliedCumulative),AmountMultipliedCumulative) AS AmountMultipliedCumulative
RESIDENT temp2;
DROP TABLE temp2;
Is this something you need in the script or front end?