Cumulative sum by week.

[SRG]

Hello Experts,

I am trying to calculate  cumulative sum by week. At the end of any week I am trying to get the totals from beginning of the time to the end of week selected. I am able to achieve a part of it by below expression

RangeSum(Above(TOTAL count({<DateType={'Dispute'}> } disputeTransactionId ), 0, RowNo(TOTAL)))

My chart  has  weekName dimension and  different measures.

However when a selection  is made on year/month/week (which are the different filters in my report) the cumulative sums are changing according to the selections made. What I am trying to achieve is when a particular week is selected I wanted to know the cumulative totals from begining of the time to that week selected. Users might also select not contiguous weeks.

Any help would be highly appreciated.

Thanks

SG.

[Vegar]

I've added some set modifiers. Try this:

RangeSum(Above(TOTAL count({<year,month,week, DateField = {"<$(=maxstring(DateField))"},DateType={'Dispute'}> } disputeTransactionId ), 0, RowNo(TOTAL)))

[SRG · Accepted solution]

Thank you for the very quick response. However the I dont see any changes in the results. For example the below chart is working fine when no selection is made on the week dimension. If any week is chosen for example 2018/27 (27 week of year 2018) my expectation is Disputes field should show 1 and cumSum filed should show 3, but when its not working. Just an FYI I am using a canonical calendar that links all my disputes,invoices. All my date dimensions are from the canonical Calendar

[Vegar · Accepted solution]

As ABOVE will look in the row above in the chart I don't think you can do what you want with an expression. 

You could however try to use an AsOf table. Take a look at the chart I created from the script below script below:

Without selections

With selection in AsOfWeek

[Master Calendar]:
LOAD
Id as disputeTransactionId, Date as DateField, Year(Date) as Year, Month(Date) as Month, WeekName(Date) as Week, 'Dispute' as DateType, Amount
Inline [
Id, Date, Amount
1,2019-03-09,78
2,2019-03-10,32
3,2019-03-12,30
4,2019-03-18,57
5,2019-03-06,59
6,2019-03-04,74
7,2019-03-02,30
8,2019-03-13,53
9,2019-03-03,65
10,2019-03-03,60
11,2019-02-13,53
12,2019-02-03,65
13,2019-02-03,60
];

tmpAsOfCal:
Load distinct
Week,
autonumber(Week,'Week') as WeekSerial
Resident [Master Calendar]
order by Week ;

// FULLL OUTER JOIN ON IT SELF
Join (tmpAsOfCal)
Load Week as AsOfWeek
Resident tmpAsOfCal ;

[AsOfCalendar]:
Load
Week,
AsOfWeek,
autonumber(AsOfWeek,'Week') - WeekSerial as WeekDiff
Resident tmpAsOfCal
Where AsOfWeek >= Week
order by Week
;

Drop Table tmpAsOfCal;

[SRG]

Vegar,

It took long for me to reply on this. First of all thank you very much for the solution. It solved part of my problem. I guess it only works for presenting weekly cumulative. If I have to do it by both monthly and weekly can you please suggest me the changes I need to do. 

I really appreciate your help on this.

Thanks

[Vegar · Accepted solution]

I guess you could change default from AsOfWeek to AsOfDateField and  add two lines into the AsOfCalendar in my previous code.

IF( Monthname(AsOfDateField)=AsOfDateField ,MonthName(AsOfDateField))as AsOfMonth,
IF( weekname(AsOfDateField)=AsOfDateField ,weekname(AsOfDateField))as AsOfWeek,

The new code look something like this

[Master Calendar]:
LOAD
Id as disputeTransactionId, Date as DateField, Year(Date) as Year, Month(Date) as Month, WeekName(Date) as Week, 'Dispute' as DateType, Amount
Inline [
Id, Date, Amount
1,2019-03-09,78
2,2019-03-10,32
3,2019-03-12,30
4,2019-03-18,57
5,2019-03-06,59
6,2019-03-04,74
7,2019-03-02,30
8,2019-03-13,53
9,2019-03-03,65
10,2019-03-03,60
11,2019-02-13,53
12,2019-02-03,65
13,2019-02-03,60
];
tmpAsOfCal:
Load distinct 
DateField,
autonumber(DateField,'DateField') as DateFieldSerial
Resident [Master Calendar]
order by DateField ;

// FULLL OUTER JOIN ON IT SELF
Join (tmpAsOfCal)
Load DateField as AsOfDateField
Resident tmpAsOfCal ;

[AsOfCalendar]:
Load 
DateField,
AsOfDateField,

IF( Monthname(AsOfDateField)=AsOfDateField ,MonthName(AsOfDateField))as AsOfMonth,
IF( weekname(AsOfDateField)=AsOfDateField ,weekname(AsOfDateField))as AsOfWeek,
autonumber(AsOfDateField,'DateField') - DateFieldSerial as DateFieldDiff
Resident tmpAsOfCal
Where AsOfDateField >= DateField
order by DateField
;

Drop Table tmpAsOfCal;

exit script;

Use AsOfWeek when accumilating on week basis and AsOfMonth when acc on month etc.

[SRG]

Thank you very much for the solution.