Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
aguitatierra
Contributor II
Contributor II

Build table with cumulative sum within date subsets

I've prepared a simplification of my work in Excel to make it easier to explain. My initial dataframe is the one with yellow background and I'd like to be able to select a date in the Dashboard, for instance, 15/05/2023 and that the table with grey background updates as shown in the image. It shows the cumulative sum of DailySpending within different registry subsets based on dates (year, month, day)

Until now I've tried with Set Analyisis, Aggr() and RangeSum(), but with no success. For the column MonthlySpending_Real my trials for the expression have been in the line of:

Aggr( Rangesum(Above(Sum({$<Type={'Real'}>} [DailySpending], 0, 12))), (Year, (NUMERIC, Ascending)), (Month, (NUMERIC, Ascending)))

Many thanks for any help! 🙂

aguitatierra_1-1684153730593.png

 

 

Labels (1)
3 Replies
Gabbar
Specialist
Specialist

As i can observe You have two Fields Monthly Spending Cums, and Yearly SpendingCums, If yes use these,
Try these expression with group as Dimension:-
Sum({< Type = {'Real'}>}DailySpending) for Daily Spending_real,
Sum({<Type = {'Real'},Date=,Month={"$(=Month(Date))"},Year={"$(=Year(Date))"}>}MonthlySpending_Cum),
Sum({<Type = {'Real'},Date=,Month=,Year={"$(=Year(Date))"}>}YearlySpending_Cum)

if not creating them in load Script will be a better method for that try this:
A:
Load * from Source;
noconcatenate
B:
load *, if (Group = previous(Group) and Type = previous(Type), DailySpending + peek(MonthlySpending),DailySpending) as MonthlySpending resident A 
order by group Asc, Type Asc, Date Asc;

Drop Table A;
noconcatenate
C:
load *, if (Group = previous(Group) and Type = previous(Type), MonthlySpending + peek(YearlySpending),MonthlySpending) as YearlySpending resident B
order by group Asc, Type Asc, Date Asc;

Drop Table B;

aguitatierra
Contributor II
Contributor II
Author

Hi Gabbar, thanks a lot for your reply. As you suggested the way forward for my case was to modify the data before loading it.

Gabbar
Specialist
Specialist

If the issue is resolved, please mark the question as marked by accepting a comment as a solution.