Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jwaligora
Creator II
Creator II

Exposing all combinations of cyclically repeating calendar values in a straight table

Hi,

For demonstration purposes, I have the following data set:

IDDTEvent
1012019-01-01 01:40:00Blue
1022019-01-01 12:40:00Green
1032019-01-01 12:41:00Blue
1042019-01-01 12:41:00Blue
1052019-01-01 18:21:00Grey
1062019-01-01 23:10:00Purple
1072019-02-01 02:50:00Red
1082019-02-01 12:33:00Red
1092019-02-01 14:00:00Black
1102019-04-01 01:30:00Blue
1112019-05-01 12:41:00Black
1122019-05-01 18:45:00Red
1132019-08-01 07:28:00Blue

 

What I need QV to display is a table with the headings:

WeekDayHourRecord Count

 

The problem is that I need ALL weekdays (Sun to Sat) to be listed, and for each of the weekdays I need ALL hours (0 to 23) to be listed. 7*24=168 rows in total.

The actual data set is rather large and convoluted, but still subject to "gaps" when filtered, resulting in certain combinations of WeekDay and Hour to not be present in the table. I fudged a solution by adding a concatenation of weekday and hour into my master calendar, and subsequently forcing "Show All Values" on the table dimension. This is non-ideal, because I end up with one dimension column instead of two...and also because there's probably a better way to address this issue.

Thanks,
J.

Labels (4)
2 Replies
Vegar
MVP
MVP

You will need to have hour and date in the same calendar table if you are to make any selection in date and/or hours.

The colde below will help you.

Transactions:
LOAD
	ID,
	Event,
	floor(DT) &  '|' & hour(DT) as %calendar,
	DT,
	1 as [No of events];
LOAD ID, 
     Timestamp#(DT, 'YYYY-DD-MM hh:mm:ss') as DT, 
     Event
FROM
	[https://community.qlik.com/t5/QlikView-App-Development/Exposing-all-combinations-of-cyclically-repeating-calendar/m-p/1553259#M440435]
	(html, codepage is 1252, embedded labels, table is @1);

for _date=date#('2019-01-01' , 'YYYY-DD-MM') to date#('2019-08-01', 'YYYY-DD-MM') step 1
	for _hour=1 to 24 step 1
		LOAD  
		$(_date)&'|'&$(_hour) as  %calendar,
		Date($(_date),'YYYY-DD-MM') as Date,
		weekday($(_date))  as Weekday,
		num($(_hour), '00') & ':00' as Hour
		AutoGenerate 1;
	next
next 

Below a pivot chart in QlikView presenting the values. Remember to uncheck Supress zero values in the chart properties.

image.png

jwaligora
Creator II
Creator II
Author

Hi,

With this approach (barring user error on my part) even with 'suppress zeros' unchecked, the data table shrinks when filters are applied. 

The business need is very specific here - the output must be 3 columns, and 168 rows. Values (column total) must be reflective of selection criteria.

Here's the code for what I'm doing (pretty much same as yours...)

DataTab:
Load 
	ID,
	Timestamp#(DT, 'YYYY-MM-DD HH:mm:ss') as DT,
	Timestamp(Floor(Timestamp#(DT, 'YYYY-MM-DD HH:mm:ss'), 1/24),'YYYY-MM-DD HH') as DH,
	Event;

LOAD ID, 
     Timestamp#(DT, 'YYYY-DD-MM hh:mm:ss') as DT, 
     Event
FROM
	[https://community.qlik.com/t5/QlikView-App-Development/Exposing-all-combinations-of-cyclically-repeating-calendar/m-p/1553259#M440435]
	(html, codepage is 1252, embedded labels, table is @1);


Temp:
Load 
	min(Floor(DT,1/24),'YYYY-MM-DD HH:mm:ss') as minDT,
	max(Ceil(DT,1/24),'YYYY-MM-DD HH:mm:ss') as maxDT	
Resident DataTab;

LET vMinDT = peek('minDT');
LET vMaxDT = peek('maxDT');

Drop table Temp;

LET vDays = vMaxDT - vMinDT ;

MC:
LOAD 
	Timestamp(Floor(TempDate, 1/24),'YYYY-MM-DD HH') as DH,
	Hour(TempDate) as [Hour],
	Date(Floor(Date(TempDate,'YYYY-MM-DD')),'YYYY-MM-DD') as [Day],
	WeekDay(TempDate) as WkDay,
	WeekDay(TempDate) & ' ' & right(Timestamp(Floor(TempDate, 1/24),'YYYY-MM-DD HH'),2) as WkDayHr,
	Dual(Date([TempDate],'YYYY') & '-' & Date([TempDate],'MMM'), MonthStart([TempDate])) as [Month],
	Year(TempDate) as [Year]
;

LOAD
    Timestamp($(vMinDT) + (RecNo()-1)/24) as TempDate
AUTOGENERATE 24 * $(vDays) + 1;

The two approaches that work for me so far (both fudge'y) are:

(1) Create a straight table with Dimension = WkDayHr and Expression = count({$} distinct ID).  Set Dimension to "Show All Values", for the Expression uncheck "Suppress Zero Values".

(2) Create a straight table with Dimensions: {WkDay, Hr} and Expression =if (count({1} distinct ID) = count({$} distinct ID), count({$} distinct ID),count({$} distinct ID)) ... yes, it says "if True then apples else apples". For the Expression, uncheck "Supress Zero Values".  "Show All Values" does NOT need to be selected for either dimension. 

The first one fails to address the business need as it only uses 2 columns, while the second one uses a seemingly unnecessarily convoluted Expression calculation.

Thanks,
J.