Dividing a Cumulative Sum by a Total in a Line Graph

DanEvans — Fri, 26 Apr 2024 10:58:46 GMT

Hi,

I have searched high and low but cannot find my answer. Hopefully someone can point me in the right direction, or even if my current model simply won't allow for it:

I need to form a line graph to show how much of a total was cleared by X time in QlikSense. I have two tables: one with invoices where each invoice appears only once and one with the receipts that clear that invoice where each invoice may have multiple receipts that relate to it; each receipt is assigned an age band which relates to how long it took for the receipt to be received relative to when the invoice was due to be cleared (i.e. day 7, day 14 etc). Each invoice has a quarter that it was due and I want to have one line of the graph for each of these quarters.

I can form a running sum of the receipts by each age band split across the quarter that they are due using:

Sum(Aggr(RangeSum(Above(Sum([Payment Amount]), 0, RowNo())), [Due Quarter], [Age Band]))

And it produces the below:

However, I now need to divide each of these by the total amount due in each quarter to the get a running % of the amount cleared by each period in time.

I can get the 'correct' number of the total due each quarter to appear as a single point on a line graph using:

Aggr(Sum([Original Gross Amount]), [Due Quarter])

However, I cannot get essentially a set of horizontal lines for each point so that they show the total for the quarter regardless of the age band of receipts which would then allow me to combine the two graphs to form a running %. Not every age band will show up for every invoice in the data.

The invoice table (although it has far more fields in reality) is like this:

Invoice Number	Due Quarter	Amount
1	Q1-2023	100
2	Q2-2023	300
3	Q3-2023	500
4	Q4-2023	150
5	Q1-2024	64
6	Q1-2023	24
7	Q2-2023	566
8	Q3-2023	122
9	Q4-2023	46
10	Q1-2024	677

And the receipts table look like this (they relate via Invoice Number):

Receipt Number	Invoice Number	Receipt Amount	Age Band
1	1	50	Due Date
2	2	150	7 Days
3	3	250	14 Days
4	4	75	Due Date
5	5	32	28 Days
6	6	12	21 Days
7	7	283	7 Days
8	8	61	Due Date
9	9	23	7 Days
10	10	338.5	14 Days
11	1	50	21 Days
12	2	150	7 Days
13	3	250	Due Date
14	4	75	7 Days
15	5	32	14 Days
16	6	12	Due Date
17	7	283	7 Days
18	8	61	14 Days
19	9	23	Due Date
20	10	338.5	28 Days

Anything anyone can do to assist me with this one, I'd appreciate it.

topic Dividing a Cumulative Sum by a Total in a Line Graph in App Development

Dividing a Cumulative Sum by a Total in a Line Graph