# 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

## 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 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 ExposureAmountExposure_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.

Labels (4)

0 Replies
Tags