topic Re: How to calculate cumulative multiplication? in App Development

How to calculate cumulative multiplication?

gnmq — Mon, 20 Dec 2021 21:08:46 GMT

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.

Re: How to calculate cumulative multiplication?

RsQK — Thu, 10 Sep 2020 08:05:27 GMT

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;

Re: How to calculate cumulative multiplication?

RsQK — Thu, 10 Sep 2020 08:44:02 GMT

Weird, already posted once, but the post somehow dissapeared. Anyway, try this:

Re: How to calculate cumulative multiplication?

RsQK — Thu, 10 Sep 2020 12:40:22 GMT

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;

Re: How to calculate cumulative multiplication?

sunny_talwar — Thu, 10 Sep 2020 12:43:57 GMT

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