topic Re: week wise bucket in QlikView

week wise bucket

pk — Sat, 16 Nov 2024 17:55:45 GMT

Hi, I'm new to Qlikview and I need help in creating week buckets-

1) if i select September month in filter then my buckets will be like below-

	Transactions from 01/09/20 to 06/09/20	Transactions from 07/09/20 to 13/09/20	Transactions from 14/09/20 to 20/09/20	Transactions from 21/09/20 to 27/09/20	Transactions from 28/09/20 to 30/09/20
Cust1	0	60.91	26.4	17.72	0
Cust2	0	560.68	567.23	514.09	520
Note-	This week will not show sum of amt bcz don’t have Monday in it	Sum of 01/09/20 to 07/09/20	Sum of 07/09/20 to 14/09/20	Sum of 14/09/20 to 21/09/20	Sum of 21/09/20 to 28/09/20

2)if my month start on Tuesday(01/09/20) then my first week will be 01/09/20 to 06/09/20 but here i do not want to show any amt

because this week does not consist Monday in it. so its amt will display in next week in 07/09/20 to 13/09/20

thanks in advance

Re: week wise bucket

Kushal_Chawda — Fri, 23 Oct 2020 12:21:19 GMT

@pk try below. Assuming that you have Date field in your Data

Data: LOAD .. ..., dual ( text(weekstart(Date,0,0)) & '-'& text(weekend(Date,0,0)), weekstart(Date,0,0)) as Week_Bucket FROM Table;

create pivot table and add Week_Bucket as column. Then use below expression

= if( weekday(Week_Bucket)='Mon', YourExpression,0)

Re: week wise bucket

pk — Mon, 26 Oct 2020 12:24:07 GMT

thanks for the immediate solution @Kushal_Chawda ,

i need more help in below scenario,
am showing report by customers, so some of customer's payment cycle is fixed like cust1 payment cycle is on every month's 25 day so my bucket will be same as week but sum(data) will display in 4th week that is 21Sept to 27Sept bucket for Sept month.(sum of 26Aug to 25th Sept Data) so please help to achieve this.

in One/single expression want to achieve this both scenario.

thanks in advance

Re: week wise bucket

pk — Tue, 27 Oct 2020 09:45:56 GMT

@sunny_talwar
can you please help me in this req

Re: week wise bucket

Kushal_Chawda — Tue, 27 Oct 2020 10:36:39 GMT

@pk please share sample data with expected output

Re: week wise bucket

pk — Wed, 28 Oct 2020 07:00:36 GMT

@Kushal_Chawda please check i have shared expected format and data

Re: week wise bucket

Brett_Bleess — Wed, 04 Nov 2020 19:14:30 GMT

@Kushal_Chawda Data attached to the thread, sorry the notifications are still not working! @pk Something else that might work might be intervalMatch, but I am not sure, so attaching some items on that, so you can check them out while Kush is trying to have a look at things.

https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptPrefixes/IntervalMatch.htm

Regards,
Brett

Re: week wise bucket

Kushal_Chawda — Wed, 04 Nov 2020 22:00:07 GMT

@pk expected output is bit different than your actual qvw file data. not sure how expected output is calculated

Re: week wise bucket

edwin — Thu, 05 Nov 2020 03:29:32 GMT

i got interested bec your 2nd week starts with the last day of your prior week and so on. there may be a way to do that but i would move that logic to the script for faster rendering:

Dates:
load date(date('1/1/2020') + RowNo()-1) as Date,
monthstart(date(date('1/1/2020') + RowNo()-1)) as Month,
weekstart(date(date('1/1/2020') + RowNo()-1),0,0) as Week,
if(isnull(peek(Month)),0,
if(peek(Month)=monthstart(date(date('1/1/2020') + RowNo()-1))
,peek(MonthIndex), peek(MonthIndex)+1)
) as MonthIndex,
//MonthWeek, numbers the weeks inside a month from 0 to 5 (max weeks in a month)
if(date(date('1/1/2020') + RowNo()-1) =monthstart(date(date('1/1/2020') + RowNo()-1)), //start of Month
0,
if(peek(Week)=weekstart(date(date('1/1/2020') + RowNo()-1),0,0),
peek(MonthWeek),
peek(MonthWeek)+1
)
) as MonthWeek,
//MonthWeekStart, indicates if the date is the start of a week inside a month
if(date(date('1/1/2020') + RowNo()-1) =monthstart(date(date('1/1/2020') + RowNo()-1)), //start of Month
1,
if(peek(Week)=weekstart(date(date('1/1/2020') + RowNo()-1),0,0),
0,
1
)
) as MonthWeekStart,

if(date(date('1/1/2020') + RowNo()-1) =monthstart(date(date('1/1/2020') + RowNo()-1)), //start of Month
if(WeekDay(date(date('1/1/2020') + RowNo()-1))='Mon',1,0),
if(peek(Week)=weekstart(date(date('1/1/2020') + RowNo()-1),0,0),
0,
if(WeekDay(date(date('1/1/2020') + RowNo()-1))='Mon',1,0)
)
) as WeekStartIsAMonday
AutoGenerate(365);

IsMonday:
load distinct MonthIndex, MonthWeek, WeekStartIsAMonday Resident Dates where MonthWeekStart=1;

inner join (Dates)
load MonthIndex, MonthWeek, WeekStartIsAMonday as WeekStartsOnAMonday Resident IsMonday;

Data: load 'A' as Cust, Date as FactDate, floor(rand()*1000)/100 as Amount Resident Dates;
concatenate(Data) load 'B' as Cust, Date as FactDate, floor(rand()*1000)/100 as Amount Resident Dates;

Weeks: load distinct Month as BridgeMonth, MonthIndex*10 + MonthWeek as BridgeWeek,WeekStartsOnAMonday as BridgeWeekStartsOnAMonday, Date as FactDate resident Dates;

//this is for the last date of prior week, since its a different week, it will have a different flag for IsAMonday so its addressed another way
NoConcatenate
tmpWeeks: load Month as BridgeMonth, MonthIndex*10 + MonthWeek-1 as BridgeWeek, Date as FactDate resident Dates
where MonthWeekStart=1 and MonthWeek>0;

inner join (tmpWeeks)
load MonthIndex*10 + MonthWeek as BridgeWeek, WeekStartIsAMonday as BridgeWeekStartsOnAMonday Resident IsMonday;

Concatenate (Weeks)
load BridgeMonth, BridgeWeek, BridgeWeekStartsOnAMonday, FactDate Resident tmpWeeks;

drop field WeekStartIsAMonday;
drop table IsMonday, tmpWeeks;
drop table Dates;

the pivot table uses the following as dimension:
Cust,
='transactions for ' & date(aggr(min(FactDate),BridgeWeek)) & ' to ' & date(aggr(max(FactDate),BridgeWeek))

Expression is just sum(Amount)