Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Cumulative Sum to fill a certain amount, then reset.

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  
ReportingPeriodDateExposureAmount
01-201901-01-20192
01-201902-01-20193
01-201904-01-20192
01-201907-01-20195
01-201908-01-20192
01-201913-01-20193
01-201916-01-20191
01-201918-01-20198
01-201922-01-20194
01-201923-01-20197

 

HEDGES  
ReportingPeriodHedgeAmountHedgeSequence
01-2019101
01-2019102
01-2019103
01-201994

 

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 ExposureAmountExposure_CumSum & AllocatedSequence)

EXPOSURES_NEW:   
PeriodDateExposureExposure_CumSumAllocatedSequence
01-201901-01-2019221
01-201902-01-2019351
01-201904-01-2019271
01-201907-01-20193101
01-201907-01-2019222
01-201908-01-2019242
01-201913-01-2019372
01-201916-01-2019182
01-201918-01-20192102
01-201918-01-2019663
01-201922-01-20194103
01-201923-01-2019774

 

Thank you.

0 Replies