topic Re: Calendar dashboard in QlikView

Calendar dashboard

Wed, 25 Nov 2020 16:16:04 GMT

Hi There,

I’m looking at creating a calendar view dashboard which is able to flag (change colour) when a value on a particular day breaches a consent level. In other words, if a value on a particular date breaches, the cell on that date is highlighted. I have looked that using a calendar object however this does not satisfy my requirements. What I would like is to have a dashboard with a monthly calendar view as seen below. Any ideas please?

Thanks,

Re: Calendar dashboard

MK_QSL — Wed, 10 Aug 2016 08:02:29 GMT

Where is the value here?

Provide sample data to work.!

Re: Calendar dashboard

Wed, 10 Aug 2016 08:14:36 GMT

Hi,

Below is a typical example of the data. As you can see, the values 1002.72 and 1502.36 are highlighted as they have breached the target of 1000. So on the calendar dashboard, the end user should see the date highlighted. Once they select the date, they will then be able to see the time of the breach.

Date	Time	Measure (units)
10/07/2016	00:00:21	99.68
10/07/2016	00:01:21	102.36
10/07/2016	00:02:21	103.43
10/07/2016	00:03:21	103.07
10/07/2016	00:04:21	102.72
10/07/2016	00:05:21	1002.72
10/07/2016	00:06:21	103.07
10/07/2016	00:07:21	103.07
10/07/2016	00:08:21	103.07
10/07/2016	00:09:21	1502.36
10/07/2016	00:10:21	102.36
10/07/2016	00:11:21	102.01

See this example from the Qlik demo site....

QlikView

Re: Calendar dashboard

Anonymous — Wed, 10 Aug 2016 08:18:34 GMT

Hi Mike, use a pivot table, dims must be week and weekday, and your expression Max(Day), on colour expression you can set a dynamic colour.

See attached xample.

Regards!!!

Re: Calendar dashboard

Wed, 10 Aug 2016 09:55:28 GMT

Manuel,

This is helpful but I cannot seem to replicate your object. See below. I do not see the full calendar!

I had to replace the field OrderDate with Date as this does not exists in my script.

Re: Calendar dashboard

Anonymous — Wed, 10 Aug 2016 10:00:41 GMT

Hi Mike, do you have a master calendar on your app?

Could you attach one example of your app?

Thanks!

Re: Calendar dashboard

Wed, 10 Aug 2016 10:04:12 GMT

Hello,

I wouldn't be able to attach the app for data protection reasons. However, I have have some date scripts below and hope it would help?......

MaxDate:

LOAD max(Date) as MaxDate

RESIDENT P_Calendar;

LET vToday=num(peek('MaxDate',0,'MaxDate'));

LET vFromDate= date($(vToday),'DD/MM/YYYY');

LET vToDate= date($(vToday),'DD/MM/YYYY');

Re: Calendar dashboard

Wed, 10 Aug 2016 10:08:34 GMT

Manuel,

Please see attached

Re: Calendar dashboard

Anonymous — Wed, 10 Aug 2016 10:10:12 GMT

Hi Mike, use this script to create a master calendar:

MaxMinDate:
LOAD max(Date) as MaxDate,
     min(Date) as MinDate
RESIDENT P_Calendar;

Let varMinDate = Num(peek('MinDate',0,'MaxMinDate'));  
Let varMaxDate = Num(peek('MaxDate',0,'MaxMinDate'));

DROP TABLE MaxMinDate;

TempCalendar:  
LOAD  
               $(varMinDate) + RowNo()-1 As Num,  
               Date($(varMinDate) + RowNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + RowNo() -1 <= $(varMaxDate);  
  
MasterCalendar:  
Load  
               TempDate AS OrderDate,  
               week(TempDate) As Week,  
               Year(TempDate) As Year,  
               Month(TempDate) As Month,  
               Day(TempDate) As Day,  
               YeartoDate(TempDate)*-1 as CurYTDFlag,  
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,  
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,  
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,  
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,  
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,  
               WeekDay(TempDate) as WeekDay  
Resident TempCalendar  
Order By TempDate ASC;  
Drop Table TempCalendar;

Regards!!

Re: Calendar dashboard

Anonymous — Wed, 10 Aug 2016 10:18:12 GMT

Hi Mike,

See attached example with your QVD data. I've added one var and one field.

Regards!!

Re: Calendar dashboard

effinty2112 — Wed, 10 Aug 2016 10:18:39 GMT

Hi Mike,

You can create a pivot table like this. In my app I have calendar with fields InvWeek and InvWeekDay. The Background colour is set to: if(sum(Qty*[Unit Price]) > 100000,RGB(250,250,210)). No invoicing on Sat or Sun in my data.

InvWeek	InvWeekDay	Mon	Tue	Wed	Thu	Fri
26						01/07/2016
27		04/07/2016	05/07/2016	06/07/2016	07/07/2016	08/07/2016
28		11/07/2016	12/07/2016	13/07/2016	14/07/2016	15/07/2016
29		18/07/2016	19/07/2016	20/07/2016	21/07/2016	22/07/2016
30		25/07/2016	26/07/2016	27/07/2016	28/07/2016	29/07/2016

A little more work can give you a straight table like this:

Mon	Tue	Wed	Thu	Fri
				01/07/2016
04/07/2016	05/07/2016	06/07/2016	07/07/2016	08/07/2016
11/07/2016	12/07/2016	13/07/2016	14/07/2016	15/07/2016
18/07/2016	19/07/2016	20/07/2016	21/07/2016	22/07/2016
25/07/2016	26/07/2016	27/07/2016	28/07/2016	29/07/2016

The first column shown has expression : Max({$<InvWeekDay = {'Mon'}>}[Invoice Date]) and Background Colour set to:

if(sum({$<InvWeekDay = {'Mon'}>}Qty*[Unit Price]) > 100000,RGB(250,250,210))

The first column is the dimension InvWeek which I have hidden using the option in the Presentation tab of the chart properties.

Hope this helps

Cheers

Andrew

Re: Calendar dashboard

Wed, 10 Aug 2016 11:19:44 GMT

Brilliant,

It's worked. I now need to work on the background colour as this is a rate of change expression.

Thanks again.

Re: Calendar dashboard

effinty2112 — Wed, 10 Aug 2016 13:01:55 GMT

Hi Mike,

Glad to be of help!

All the best

Andrew

Mon	Tue	Wed	Thurs	Fri	Sat	Sun
1	2	3	4	5	6	7
8	9	10	11	12	13	14
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31