Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
msmSahaj
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

Labels (3)
2 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

Vegar
MVP
MVP

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.