Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
Please help me on this. Your response is highly appreciated!
Neville
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, !
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
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,
Dear Kh fou,
I wiil be much delighted, if you send me the working on the note pad
Thanks
Neville
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,
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
Please help me to get the averge
Thanks
Neville
here's the function for calculating the average: