Cumulative Sum to fill a certain amount, then reset.

johan_joubert — Sat, 16 Nov 2024 03:37:59 GMT

Hey Qlik gurus,

Wondering if someone could assist with a logical issue I have at the moment. Essentially what I have is two straight tables, one called EXPOSURES, the other HEDGES. The logical requirements I need to meet are as follows: (PS. Load script and/or set analysis solution suggestions are fine, I have hacked at both with no success).

1. I need to do a cumulative sum of all the exposures (ExposureAmount) in table "Exposures" up to the point where the first hedge amount (HedgeAmount) in table "HEDGES" is fully utilized (HedgeSequence = 1). If the cumulative sum of ExposureAmounts are greater then the HedgeAmount, then it needs to be split so that the cumulative sum of ExposureAmount ends at the same value of HedgeAmount.

2. Once the HedgeAmount has been filled, then I need the remainder of the ExposureAmount to be carried over and used to start filling the second HedgeAmount.

This is quite tricky to explain, so I will give an example below:

EXPOSURES
ReportingPeriod	Date	ExposureAmount
01-2019	01-01-2019	2
01-2019	02-01-2019	3
01-2019	04-01-2019	2
01-2019	07-01-2019	5
01-2019	08-01-2019	2
01-2019	13-01-2019	3
01-2019	16-01-2019	1
01-2019	18-01-2019	8
01-2019	22-01-2019	4
01-2019	23-01-2019	7

HEDGES
ReportingPeriod	HedgeAmount	HedgeSequence
01-2019	10	1
01-2019	10	2
01-2019	10	3
01-2019	9	4

LOGIC:

cumulative sum of ExposureAmount on 1 Jan = 2, so still less than the HedgeAmount(10) where HedgeSequence = 1 , ExposureAmount = 2 and Exposure_CumSum = 2 ... (2) and AllocatedSequence = 1

cumulative sum of ExposureAmount on 2 Jan = 3, so still less than the HedgeAmount(10) where HedgeSequence = 1 , ExposureAmount = 3 and Exposure_CumSum = 5 ... (2 + 3) and AllocatedSequence = 1

cumulative sum of ExposureAmount on 4 Jan = 2, so still less than the HedgeAmount(10) where HedgeSequence = 1 , ExposureAmount = 2 and Exposure_CumSum = 7 ... (2 + 3 + 2) and AllocatedSequence = 1

cumulative sum of ExposureAmount on 7 Jan = 5, this is greater than the HedgeAmount(10) where HedgeSequence = 1 , thus ExposureAmount = 1 and Exposure_CumSum = 10 ... (2 + 3 + 2 + 3) and AllocatedSequence = 1

now... the remainder of the ExposureAmount on 7 Jan (5 - 3 = 2) needs to be allocated as follows:

ExposureAmount = 2, Exposure_CumSum = 2, AllocatedSequence = 2

... so the process continues until all ExposureAmounts have been Accumulated to fill the various HedgeAmounts.

Desired outcome (Fields ExposureAmount & Exposure_CumSum & AllocatedSequence)

EXPOSURES_NEW:
Period	Date	Exposure	Exposure_CumSum	AllocatedSequence
01-2019	01-01-2019	2	2	1
01-2019	02-01-2019	3	5	1
01-2019	04-01-2019	2	7	1
01-2019	07-01-2019	3	10	1
01-2019	07-01-2019	2	2	2
01-2019	08-01-2019	2	4	2
01-2019	13-01-2019	3	7	2
01-2019	16-01-2019	1	8	2
01-2019	18-01-2019	2	10	2
01-2019	18-01-2019	6	6	3
01-2019	22-01-2019	4	10	3
01-2019	23-01-2019	7	7	4

Thank you.

topic Cumulative Sum to fill a certain amount, then reset. in App Development

Cumulative Sum to fill a certain amount, then reset.