cancel
Showing results for
Did you mean:
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! 🙂

Labels (1)
• ### Set Analysis

3 Replies
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;

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.

Specialist

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