How to calculate cumulative multiplication?

[gnmq]

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.

[RsQK]

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;

[RsQK]

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;

[RsQK]

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;

[sunny_talwar]

Is this something you need in the script or front end?