Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

How to calculate the weekly collections & average weekly collections

Hi

Please see the attached Excel & the P/T where I need to be able to calculate weekly collections as it is & their average. Data given is from 1st January up to today.  I need to see the collection per each week per Month so that I need to create a list box namely week_collections (1st Week, 2nd Week,3rd Week & 4th Week etc ) thereby on a clik of 1st week say January, it should show me January 1st week collections, when it is 2nd week, 2nd week collections to be shown etc.

I think if we build up a week bucket through if statement in the script my desired out put could be made. But I fail to do this accurately hence please help me on this.

Regds

Neville

1 Solution

Accepted Solutions
kfoudhaily
Partner - Creator III
Partner - Creator III

COLLECTIONS:

LOAD DATE,

     TYPE,

     REF_NUMBER,

     DEB_NUMBER,

     INSURED,

     CH_NO,

     BANK,

     [V/NO],

     TOTAL as Solde

FROM

COLLECTIONS.xlsx

(ooxml, embedded labels, table is Sheet1);

//the folowing is to create a perpetual calender

Let vStartDate = Floor(MakeDate(2017,1,1));

Let vEndDate = Floor(Today());

Let vDiff = vEndDate-vStartDate +1;

CALENDAR:

LOAD

*,

Year & '' & Month  As %YearMonth,

Year & '-' & Quarter As YearQuarter,

WeekYear & '-' & Num(Week, '00') As YearWeek;

LOAD

DateID,

Date(DateID) As DATE,

Year(DateID) As Year,

Num(Month(DateID), '00') As Month,

Month(DateID) As Monthh,

Day(DateID) As Day,

Week(DateID) As Week,

'Q' &Ceil(Month(DateID)/3) As Quarter,

WeekYear(DateID) As WeekYear,

-Year2Date(DateID) As YTD_Flag,

-Year2Date(DateID, -1) As LYTD_Flag,

weekday(DateID) AS Weekday,

autonumber(weekyear(DateID) &'|' & week(DateID), '_WeekSerial') AS _WeekSerial;

LOAD

RecNo()-1+$(vStartDate) As DateID

AutoGenerate($(vDiff));

please mark response as helpfull if it's ok for you!

regards,

QlikView Qlik Sense consultant

View solution in original post

11 Replies
nevilledhamsiri
Specialist
Specialist
Author

Please help me on this. Your response is highly appreciated!

Neville

kfoudhaily
Partner - Creator III
Partner - Creator III

Hello,

please check qvw attached,

I had to rename the field 'TOTAL' (in the script) because it's a key word in qlikview script

hope it helps!

regards, !

QlikView Qlik Sense consultant
nevilledhamsiri
Specialist
Specialist
Author

Dear Friend

If possible please send me the expression & working on a note pad. I am using a Personnel edition

Thanks for your response

Neville

kfoudhaily
Partner - Creator III
Partner - Creator III

COLLECTIONS:

LOAD DATE,

     TYPE,

     REF_NUMBER,

     DEB_NUMBER,

     INSURED,

     CH_NO,

     BANK,

     [V/NO],

     TOTAL as Solde

FROM

COLLECTIONS.xlsx

(ooxml, embedded labels, table is Sheet1);

//the folowing is to create a perpetual calender

Let vStartDate = Floor(MakeDate(2017,1,1));

Let vEndDate = Floor(Today());

Let vDiff = vEndDate-vStartDate +1;

CALENDAR:

LOAD

*,

Year & '' & Month  As %YearMonth,

Year & '-' & Quarter As YearQuarter,

WeekYear & '-' & Num(Week, '00') As YearWeek;

LOAD

DateID,

Date(DateID) As DATE,

Year(DateID) As Year,

Num(Month(DateID), '00') As Month,

Month(DateID) As Monthh,

Day(DateID) As Day,

Week(DateID) As Week,

'Q' &Ceil(Month(DateID)/3) As Quarter,

WeekYear(DateID) As WeekYear,

-Year2Date(DateID) As YTD_Flag,

-Year2Date(DateID, -1) As LYTD_Flag,

weekday(DateID) AS Weekday,

autonumber(weekyear(DateID) &'|' & week(DateID), '_WeekSerial') AS _WeekSerial;

LOAD

RecNo()-1+$(vStartDate) As DateID

AutoGenerate($(vDiff));

please mark response as helpfull if it's ok for you!

regards,

QlikView Qlik Sense consultant
nevilledhamsiri
Specialist
Specialist
Author

Dear Kh fou,

I wiil be much delighted, if you send me the working on the note pad

Thanks

Neville

kfoudhaily
Partner - Creator III
Partner - Creator III

You can copy and past the script in my previous reply on notepad or any other program.

Please mark response as helpfull if it's ok for you!

regards,

QlikView Qlik Sense consultant
nevilledhamsiri
Specialist
Specialist
Author

Thanks Kh Fou,

It is simply mazing! Of course I had not learn this before. I will right away mark this correct answer. But please also help me to get the average of each week. If I write expression as AVG(Solde) will it give me the answer for each week?

Thanks

Neville

nevilledhamsiri
Specialist
Specialist
Author

Please help me to get the averge

Thanks

Neville

kfoudhaily
Partner - Creator III
Partner - Creator III

here's the function for calculating the average:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/StatisticalAgg...

QlikView Qlik Sense consultant