topic Cumulative sum by week. in QlikView

Cumulative sum by week.

SRG — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Cumulative sum by week.

Vegar — Thu, 02 May 2019 04:27:00 GMT

I've added some set modifiers. Try this:

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

Re: Cumulative sum by week.

SRG — Thu, 02 May 2019 14:07:45 GMT

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

Re: Cumulative sum by week.

Vegar — Thu, 02 May 2019 15:09:35 GMT

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

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;

Re: Cumulative sum by week.

SRG — Sun, 02 Jun 2019 17:40:04 GMT

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

Re: Cumulative sum by week.

Vegar — Sun, 02 Jun 2019 19:56:26 GMT

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.

Re: Cumulative sum by week.

SRG — Thu, 13 Jun 2019 15:56:11 GMT

Thank you very much for the solution.