topic Re: how to make multiple date dimention in QlikView

how to make multiple date dimention

Fri, 15 Nov 2013 06:36:08 GMT

Hi All,

I have one access_log table.

accessed_time	user_id
2013/11/1	1
2013/11/2	2
2013/11/5	1
2013/11/10	1

I want to count everyday's unique accessed users in past 7 days, like below:

7days	unique_users
11/1 - 11/7	30
11/2 - 11/8	10
11/3 - 11/9	11
11/4 - 11/10	15
11/5 - 11/11	18

I tried a lot of approach, to target multiple datetime in one dimension record , using above function, referencing dimension value from expression, etc... But all I did didn't work

Is it possible?

Abe

Re: how to make multiple date dimention

kaushiknsolanki — Fri, 15 Nov 2013 06:40:24 GMT

Hi,

Why dont you create a Master Calender and then use that as a dimension to your chart.

Kindly provide sample data.

Regards,

Kaushik Solanki

Re: how to make multiple date dimention

hic — Fri, 15 Nov 2013 08:22:54 GMT

You should create a Master Calendar, just as Kaushik suggests. There are many resources on QlikCommunity that describes this, e.g. http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

In the master calendar, you should create a field for your 7-day periods, e.g. using Week() or WeekStart() with an offset. This field can then be used as dimension.

HIC

Re: how to make multiple date dimention

Mon, 18 Nov 2013 01:49:35 GMT

Thnks!

I've created Master Calendar. It seems really useful.

but how to make 7-day periods dimension ?

Re: how to make multiple date dimention

hic — Mon, 18 Nov 2013 08:33:24 GMT

All you need to do is to create a field (in your master calendar) that specifies to which 7-day period this date belongs. You can for instance use

Week(Date)

which will give you the week number (week starts on Monday), or

WeekStart(Date,0,-1)

which will give you the date of the first week day (week starts on Sunday). By changing the third parameter, you can make the "week" begin any day you want.

HIC

Re: how to make multiple date dimention

Mon, 18 Nov 2013 11:58:07 GMT

Finally I made it

I made master calendar and date_dimension table as below:

master calendar

LOAD

gen_date as DateId,

year(gen_date) as ACCESSED_TIME_year,

month(gen_date) as ACCESSED_TIME_Month,

day(gen_date) as ACCESSED_TIME_Day,

weekday(gen_date) as ACCESSED_TIME_WeekDay,

week(gen_date) as ACCESSED_TIME_Week

;

LOAD

Date(floor(od_min + iterno() - 1),'YYYY-MM-DD') as gen_date

WHILE iterno() <= od_max - od_min + 1;

LOAD

min(ACCESSED_TIME) as od_min,

max(ACCESSED_TIME) as od_max

RESIDENT access_log

GROUP BY 1;

dimension table

LOAD

distinct

DateId - IterNo() + 1 as DateId,

DateId as DateId_dim

resident master_Calendar

while DateId - IterNo() + 1 > DateId -7;

One DateId has 7 days. Using DateId as dimension I've got what I need.

Thanks all!