topic Re: Running Total Unbalanced Data in QlikView

Running Total Unbalanced Data

villegasi03 — Sat, 17 Feb 2024 00:06:48 GMT

I was looking for a better way to script a running total and came across this post using a very simple method. The example table shown in the post is balanced.

Location	Date	Revenue	Running Total
A	Jan-13	$ 1,000	$ 1,000
A	Feb-13	$ 1,100	$ 2,100
A	Mar-13	$ 1,200	$ 3,300
A	Apr-13	$ 1,300	$ 4,600
B	Jan-13	$ 1,400	$ 1,400
B	Feb-13	$ 1,500	$ 2,900
B	Mar-13	$ 1,600	$ 4,500
B	Apr-13	$ 1,700	$ 6,200
C	Jan-13	$ 1,800	$ 1,800
C	Feb-13	$ 1,900	$ 3,700
C	Mar-13	$ 2,000	$ 5,700
C	Apr-13	$ 2,100	$ 7,80

I am looking for suggestions as to how to handle a running total when for example one of the locations doesn't have revenue but want to continue the running total through the current month.

Location	Date	Revenue	Running Total
A	Jan-13	$ 1,000	$ 1,000
A	Feb-13	$ 1,100	$ 2,100
A	Mar-13	$ 1,200	$ 3,300
B	Jan-13	$ 1,400	$ 1,400
B	Feb-13	$ 1,500	$ 2,900
B	Mar-13	$ 1,600	$ 4,500
B	Apr-13	$ 1,700	$ 6,200
C	Jan-13	$ 1,800	$ 1,800
C	Feb-13	$ 1,900	$ 3,700
C	Mar-13	$ 2,000	$ 5,700

Location A and C is missing April.

Would you OUTTER JOIN the full list of months first?

Re: Running Total Unbalanced Data

Vegar — Sat, 17 Feb 2024 06:04:30 GMT

If I don't know which Loacation/period combination that is missing revenue then you could do a outer join with all expected combinations of location/period.

Make sure your period/location table thar you create for joining have unique values so you don't end up duplicating your existing rows.

Another approach could be to concatenate rows with the missing combinations, but that might not be the best solution in your case, that is an efficient method when it is only a one field criteria.

Re: Running Total Unbalanced Data

villegasi03 — Sun, 18 Feb 2024 03:00:05 GMT

Thank you sir! I loaded distinct the locations and outer join the distinct month-years as you suggested. That worked perfectly. Thank you for your assistance.