Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Weekly Data Representation

I have a data set where for a month daily level transaction is captured per Unit wise.

I need to segregate this data and show it in Weekly basis.

Input:

UnitIDMonthDateSales
Sales20166/29/2019 0:00120
Sales20166/15/2019 0:00122
Sales20166/22/2019 0:00124
Sales20166/24/2019 0:00126
Sales20166/19/2019 0:00154
Finance20266/22/2019 0:00182
Finance20266/11/2019 0:00210
Finance20266/18/2019 0:00238
Finance20266/15/2019 0:00266
Finance20266/4/2019 0:00294
Finance20266/25/2019 0:00322
Finance20266/8/2019 0:00350
HR20366/17/2019 0:00378
HR20366/18/2019 0:00406
HR20366/10/2019 0:00434
HR20366/26/2019 0:00462
HR20366/11/2019 0:00490
HR20366/25/2019 0:00518
HR20366/7/2019 0:00546
ADMIN20466/27/2019 0:00574
ADMIN20466/2/2019 0:00602
ADMIN20466/9/2019 0:00630
ADMIN20466/8/2019 0:00658
ADMIN20466/1/2019 0:00686
ADMIN20466/30/2019 0:00714
ADMIN20466/17/2019 0:00742
ADMIN20466/15/2019 0:00770
ADMIN20466/3/2019 0:00798
ADMIN20466/22/2019 0:00826
ADMIN20466/29/2019 0:00854
EXECUTIVE20566/1/2019 0:00882
EXECUTIVE20566/8/2019 0:00910
EXECUTIVE20566/20/2019 0:00938
EXECUTIVE20566/16/2019 0:00966
EXECUTIVE20566/13/2019 0:00994
EXECUTIVE20566/23/2019 0:001022
EXECUTIVE20566/28/2019 0:001050
EXECUTIVE20566/25/2019 0:001078
EXECUTIVE20566/27/2019 0:001106

 

Output:

I require output in the below format. Kindly help

Week will be start from first wednesday of the month and will continue like this till last tuesday of that month.

Please help me to build a dynamic solution. THis is only one month data

UnitWeek EndingSum(Sales)
Sales11-Jun0
Sales18-Jun122
Sales25-Jun278
Finance11-JunSO ON
Finance18-JunSO ON
Finance25-JunSO ON
SO ONSO ONSO ON
SO ONSO ONSO ON
SO ONSO ONSO ON
SO ONSO ONSO ON
SO ONSO ONSO ON
SO ONSO ONSO ON
SO ONSO ONSO ON
2 Replies
Master II
Master II

Hi,

why not IntervalMatch ?

I propose the following scenario:

1- in the script (and this is the most not geek part)
create a calendar which you specify the Weeks Ending (because a week that ends on Wednesday does not make the task any easier).
the script is in the form:

 

WeekEnd:
LOAD Date#(Start,'MM/DD/YYYY') as Start_New,Date#(End,'MM/DD/YYYY') as End_New, * INLINE [
Start , End, Week Ending
06/05/2019, 06/11/2019, 11-Jun
06/12/2019, 06/18/2019, 18-Jun
06/19/2019, 06/25/2019, 25-Jun

];

 

then an intervalMatch with your data,

So the script will look like this:

 

Event:
LOAD Date#(Date,'MM/DD/YYYY') as Date_New,* INLINE [
    Unit, ID, Month, Date, Sales
    Sales, 201, 6, 6/29/2019, 120
    Sales, 201, 6, 6/15/2019, 122
    Sales, 201, 6, 6/22/2019, 124
    Sales, 201, 6, 6/24/2019, 126
    Sales, 201, 6, 6/19/2019, 154
    Finance, 202, 6, 6/22/2019, 182
    Finance, 202, 6, 6/11/2019, 210
    Finance, 202, 6, 6/18/2019, 238
    Finance, 202, 6, 6/15/2019, 266
    Finance, 202, 6, 6/4/2019, 294
    Finance, 202, 6, 6/25/2019, 322
    Finance, 202, 6, 6/8/2019, 350
    HR, 203, 6, 6/17/2019, 378
    HR, 203, 6, 6/18/2019, 406
    HR, 203, 6, 6/10/2019, 434
    HR, 203, 6, 6/26/2019, 462
    HR, 203, 6, 6/11/2019, 490
    HR, 203, 6, 6/25/2019, 518
    HR, 203, 6, 6/7/2019, 546
    ADMIN, 204, 6, 6/27/2019, 574
    ADMIN, 204, 6, 6/2/2019, 602
    ADMIN, 204, 6, 6/9/2019, 630
    ADMIN, 204, 6, 6/8/2019, 658
    ADMIN, 204, 6, 6/1/2019, 686
    ADMIN, 204, 6, 6/30/2019, 714
    ADMIN, 204, 6, 6/17/2019, 742
    ADMIN, 204, 6, 6/15/2019, 770
    ADMIN, 204, 6, 6/3/2019, 798
    ADMIN, 204, 6, 6/22/2019, 826
    ADMIN, 204, 6, 6/29/2019, 854
    EXECUTIVE, 205, 6, 6/1/2019, 882
    EXECUTIVE, 205, 6, 6/8/2019, 910
    EXECUTIVE, 205, 6, 6/20/2019, 938
    EXECUTIVE, 205, 6, 6/16/2019, 966
    EXECUTIVE, 205, 6, 6/13/2019, 994
    EXECUTIVE, 205, 6, 6/23/2019, 1022
    EXECUTIVE, 205, 6, 6/28/2019, 1050
    EXECUTIVE, 205, 6, 6/25/2019, 1078
    EXECUTIVE, 205, 6, 6/27/2019, 1106
];

WeekEnd:
LOAD Date#(Start,'MM/DD/YYYY') as Start_New,Date#(End,'MM/DD/YYYY') as End_New, * INLINE [
Start , End, Week Ending
06/05/2019, 06/11/2019, 11-Jun
06/12/2019, 06/18/2019, 18-Jun
06/19/2019, 06/25/2019, 25-Jun

];
Inner Join IntervalMatch ( Date_New )
 
LOAD Start_New, End_New
Resident WeekEnd;

 

And the result :

datt.PNG

 

for '-' values, it's because my calendar is not complete

 

Regards,

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Partner
Partner

I just use date function in the load statement. A simple one line solution

Date(WeekEnd("Date"),'DD-MMM') as WeekEndDate

It works like a charm