Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 |
Thanks,
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
Where is the value here?
Provide sample data to work.!
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....
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!!!
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.
Hi Mike, do you have a master calendar on your app?
Could you attach one example of your app?
Thanks!
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');
Manuel,
Please see attached
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!!
Hi Mike,
See attached example with your QVD data. I've added one var and one field.
Regards!!