topic Re: Query with dynamic bucketing Weekdate in QlikView

Query with dynamic bucketing Weekdate

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am trying to create a chart that displays count of recorded dates for each dimension in a bucketed week, from current date (eg. current date -previous 7days would be Week1 , previous 8 - 14 days would be Week2, etc.).. something like below;

Dimension	Week 1(0-7 days)	Week 2(8-14 days)	Total Dates
A	50	10	60
B		20	20
C	15		15

I believe Interval match would be needed with week(today()), but not sure how to put it to work..

Appreciate any help on it.

Cheers.

Re: Query with dynamic bucketing Weekdate

felipedl — Wed, 16 Aug 2017 17:20:26 GMT

Hi Priyanka,

Doesnt necessarily need the intervalmatch to do it.

Attached an example with some dummy data.

Hope this is what you need.

Felipe.

Re: Query with dynamic bucketing Weekdate

felipedl — Wed, 16 Aug 2017 17:37:25 GMT

Cant edit my post, so i'm pasting the code since you can be using sense rather than View.

sample:

Load * Inline

[

Id,Date,Value

A,01/07/2017,1

A,02/07/2017,2

A,03/07/2017,3

B,04/07/2017,4

B,05/07/2017,5

A,06/07/2017,6

A,07/07/2017,7

A,08/07/2017,8

A,09/07/2017,9

F,10/07/2017,10

B,11/07/2017,11

C,12/07/2017,12

D,13/07/2017,13

E,14/07/2017,13

A,15/07/2017,14

A,16/07/2017,12

C,17/07/2017,23

E,18/07/2017,2

F,19/07/2017,2

E,20/07/2017,11

B,21/07/2017,1

F,22/07/2017,1

B,23/07/2017,2

C,24/07/2017,2

D,25/07/2017,3

E,26/07/2017,54

A,27/07/2017,56

A,28/07/2017,7

C,29/07/2017,5

E,30/07/2017,4

F,31/07/2017,5

B,01/08/2017,6

C,02/08/2017,54

D,03/08/2017,4

E,04/08/2017,44

A,05/08/2017,4

A,06/08/2017,4122

C,07/08/2017,3

E,08/08/2017,1

A,09/08/2017,132

A,10/08/2017,1

A,11/08/2017,13

A,12/08/2017,123

A,13/08/2017,23

A,14/08/2017,1323

A,15/08/2017,123

];

for i = 1 to 100

Load

Today()-$(i) as T,

'Week '& floor(($(i)/7+1)) as Week

AutoGenerate(1);

next i;

left join(sample)

Load

T as Date,

Week

Resident x;

drop table x;