# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Contributor III

## How to calculate cumulative multiplication?

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.

Labels (2)

• ### SaaS

4 Replies
Contributor III

Try this:

``````temp:
AUTONUMBER(Date & '|' & Product) as ID.#key;

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:
DATE(MIN(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MinDate,
DATE(MAX(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MaxDate
AUTOGENERATE FIELDVALUECOUNT('Date');

tempMissingDates2:
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:

LEFT JOIN (tempMissingDates2) LOAD * RESIDENT tempProducts; DROP TABLE tempProducts;

CONCATENATE (temp)
Date,
Product,
[ID2.#key] AS [ID.#key],
1 AS Amount
WHERE NOT EXISTS ([ID.#key],[ID2.#key]);

Date,
Product,
AUTONUMBER(Date & '|' & Product) as ID2.#key
RESIDENT tempMissingDates2;

DROP TABLE tempMissingDates2;

temp2:
ROWNO() AS Row;

Amount as AmountMultipliedCumulative
RESIDENT temp
ORDER BY Product ASC,Date ASC;

DROP TABLE temp;

temp3:
Date,
Product,
Amount,
IF(PREVIOUS(Product)=Product,RANGESUM(PEEK(AmountMultipliedCumulative)*AmountMultipliedCumulative),AmountMultipliedCumulative) AS AmountMultipliedCumulative
RESIDENT temp2;

DROP TABLE temp2;``````

Contributor III

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

``````temp:
AUTONUMBER(Date & '|' & Product) as ID.#key;

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:
DATE(MIN(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MinDate,
DATE(MAX(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MaxDate
AUTOGENERATE FIELDVALUECOUNT('Date');

tempMissingDates2:
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:

LEFT JOIN (tempMissingDates2) LOAD * RESIDENT tempProducts; DROP TABLE tempProducts;

CONCATENATE (temp)
Date,
Product,
[ID2.#key] AS [ID.#key],
1 AS Amount
WHERE NOT EXISTS ([ID.#key],[ID2.#key]);

Date,
Product,
AUTONUMBER(Date & '|' & Product) as ID2.#key
RESIDENT tempMissingDates2;

DROP TABLE tempMissingDates2;

temp2:
ROWNO() AS Row;

Amount as AmountMultipliedCumulative
RESIDENT temp
ORDER BY Product ASC,Date ASC;

DROP TABLE temp;

temp3:
Date,
Product,
Amount,
IF(PREVIOUS(Product)=Product,RANGESUM(PEEK(AmountMultipliedCumulative)*AmountMultipliedCumulative),AmountMultipliedCumulative) AS AmountMultipliedCumulative
RESIDENT temp2;

DROP TABLE temp2;``````

Contributor III

Try this:

``````temp:
AUTONUMBER(Date & '|' & Product) as ID.#key;

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:
DATE(MIN(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MinDate,
DATE(MAX(FIELDVALUE('Date',Recno())),'DD.MM.YYYY') AS MaxDate
AUTOGENERATE FIELDVALUECOUNT('Date');

tempMissingDates2:
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:

LEFT JOIN (tempMissingDates2) LOAD * RESIDENT tempProducts; DROP TABLE tempProducts;

CONCATENATE (temp)
Date,
Product,
[ID2.#key] AS [ID.#key],
1 AS Amount
WHERE NOT EXISTS ([ID.#key],[ID2.#key]);

Date,
Product,
AUTONUMBER(Date & '|' & Product) as ID2.#key
RESIDENT tempMissingDates2;

DROP TABLE tempMissingDates2;

temp2:
Amount as AmountMultipliedCumulative
RESIDENT temp
ORDER BY Product ASC,Date ASC;

DROP TABLE temp;

temp3: