how to make multiple date dimention

[#1]

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

[kaushiknsolanki]

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   

[hic]

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

[#4]

Thnks!

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

but how to make 7-day periods dimension ?

[hic]

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

[#6 · Accepted solution]

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!