Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Time stamp Drill down for last year, month, week

Hi Experts,

How do i created drill down of  year, month, week, days for below timestamp column. 

Capture.PNG

2 Replies
Highlighted
Partner
Partner

Re: Time stamp Drill down for last year, month, week

You need to make a Master calendar table which based on your timestamp/date field . This will have the year month etc.
Then create a drill down group Year--> Month-->Week

You can get the theory from youtube video
use the script in the link below (at the very end)
https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

 On another note unless you need the time portion of your data (especially if data size is high) would recommend removing the timestamp using floor function

Highlighted
Partner
Partner

Re: Time stamp Drill down for last year, month, week

In your transaction table where you have the timestamp create a new field called %date like the script below.

Transactions:
LOAD
Dimension1, 
Dimension2,
Timestamp, 
date(SubField(Timestamp, ' ',1)) as %date 
FROM YourSource;

Then, after you created this field, run the code seen below to create an master calendar.

for each _date in FieldValueList ('%date')
  MasterCalendar:
LOAD '$(_date)' as %date, year('$(_date)') as Year, month('$(_date)') as Month, Week('$(_date)') as Week, Weekday('$(_date)') as Weekday AutoGenerate 1; next

You can expand the fields in this calendar according to your own need ant taste. (Google search for  master calendar Qlik to find other more detailed master calendars.

Take a look at my earlier comment on how to create drill down dimensions. Be aware when using week in drill down from month, a week can belong to two months. 

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes