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: 
quiquehm
Contributor III
Contributor III

Schedule Chart with calendar

Hi everyone, I wonder if you could give me some guidance here. I am trying to build an "easy" visualization for tasks , using a pivot table to display different tasks with durations across a master calendar . The steps to build the datamodel seem simple , but for whatever reason I am stuck :

First I create a Calendar using DateMin and DateMax as the range. DateMin is calculated from the start date field ..and similarly DateMax is calculated from the finish date field. I attached a simple excel sheet with the source data

Second I need to create reference dates for every task from start date to finish date . This is what I call date_fill . The problem is the reference dates are created considering all calendar dates ( including weekends ) ...as this is where I am stuck. I need those reference dates to ignore weekends , as no tasks will be done in weekends.

Third I do a left join with the Calendar ...so I have all dates in my chart ( continuos axis , all dates shown regardless if there are no tasks done in certain dates ).

I may have done something wrong in the script ( or probably there is a better/easier way to do this ) ...as for whatever reason I can´t format the weekend dates in the chart ( to put them in bold red for example ) ...it looks like it does not recognize the weekend dates as a weekend .

I attach the qvw file and the source data. There is a "expected result" tab in the excel sheet for reference. Hope someone can give me some help on what I am doing wrong here ...or if it´s an easier way to do this.

Appreciate any help ! Thank you all

1 Solution

Accepted Solutions
Kushal_Chawda

As you have taken Job and Date in dimension, you should have all possible combination if Dates for each job. So you need to cross join Date with Job and then you can join it with actual data. Then this condition works properly.

Tasks:

LOAD *,

           Floor(date_Calendar) as date_key;

Load

    Job,

    Task_nr,

    Job&Task_nr    as key,

    Task_desc,

    date_start,

    date_start+IterNo()-1 as date_Calendar,

    date_finish

FROM Schedule.xlsx (ooxml, embedded labels, table is Data)

While date_start+IterNo()-1<=date_finish;

MinMax:

LOAD max(Date) as MaxDate,

           min(Date) as MinDate;

LOAD FieldValue('date_Calendar',RecNo()) as Date

AutoGenerate FieldValueCount('date_Calendar');

let vMinDate = Peek('MinDate',0,'MinMax');

let vMaxDate = Peek('MaxDate',0,'MinMax');

DROP Table MinMax;

Calendar:

LOAD *,

          WeekDay(date_key) as  Weekday_Calendar;

LOAD floor($(vMinDate)+IterNo()-1) as date_key

AutoGenerate 1

While $(vMinDate)+IterNo()-1<=$(vMaxDate);

Left Join(Calendar)

LOAD Distinct Job

Resident Tasks;

Join(Tasks)

LOAD *

Resident Calendar;

DROP Table Calendar;

View solution in original post

9 Replies
Kushal_Chawda

try this

Tasks:

Load

    Job,

    Task_nr,

    Job&Task_nr    as key,

    Task_desc,

    date_start,

    date(date_start+IterNo()-1) as date_Calendar,

    date_finish

FROM Schedule.xlsx (ooxml, embedded labels, table is Data)

While date_start+IterNo()-1<=date_finish;

Calender:

LOAD *,

          WeekDay(date_Calendar) as Weekday_Calendar;

LOAD Distinct date_Calendar

Resident Tasks;

Expression:

=if(Match(Weekday_Calendar,'Sat','Sun'), Null(),Task_nr)

text color expression

=if(Match(Weekday_Calendar,'Sat','Sun'), LightRed(),Black())

quiquehm
Contributor III
Contributor III
Author

‌Thank you  Kushal. The only thing I am afraid I need one extra step. As you see on your printsceeen 23/Mar is missing and also there is a gap from 29/Mar to 3/Apr  . This is why I said I needed a left join with a master calendar to be able to see all these missing dates. Is my understanding correct ? How could I add that calendar? Just at the end of the script ? Can you suggest that piece of code ?

APpreciate your help

MAny thanks !!

Kushal_Chawda

may be like below

Tasks:

Load

    Job,

    Task_nr,

    Job&Task_nr    as key,

    Task_desc,

    date_start,

    date(date_start+IterNo()-1,'DD-MMM-YYYY') as date_Calendar,

    date_finish

FROM Schedule.xlsx (ooxml, embedded labels, table is Data)

While date_start+IterNo()-1<=date_finish;

MinMax:

LOAD max(Date) as MaxDate,

           min(Date) as MinDate;

LOAD FieldValue('date_Calendar',RecNo()) as Date

AutoGenerate FieldValueCount('date_Calendar');

let vMinDate = Peek('MinDate',0,'MinMax');

let vMaxDate = Peek('MaxDate',0,'MinMax');

DROP Table MinMax;

Join(Tasks)

LOAD *,

          WeekDay(date_Calendar) as  Weekday_Calendar;

LOAD date($(vMinDate)+IterNo()-1,'DD-MMM-YYYY') as date_Calendar

AutoGenerate 1

While $(vMinDate)+IterNo()-1<=$(vMaxDate);

Final:

NoConcatenate

LOAD *,

          if(IsNull(Job),Peek(Job_new),Job) as Job_new

Resident Tasks

Order by date_Calendar;

DROP Table Tasks;

Add Job_new in dimension


quiquehm
Contributor III
Contributor III
Author

Many thanks again Kushal, very very close to the expected result. One thing I still notice is for example 25 and 26 /March are indeed weekend, but for whatever reason they don´t show red in the chart .

Missing weekend.PNG

I have a table where you see both date_Calendar and Weekday_Calendar fields and on that table the formatting works correct ...these 2 days are shown in red

Red weekend.PNG

There is also something weird when you apply a filter and select for example Bedroom in the Job field, you see some dates are not shown on the chart ( March 23,24,25,26,30,31 ) , Apr 01,02

Missing dates.PNG

This is why I am confused, the expected result looks simple , but why is it so difficult to make it work ? I am pulling my hair off ...the more I look into it the more confused I am ...where can be the problem on that formatting ?...and why when you select one of the Jobs there are missing dates in the chart...

Again, many many thanks for your time and help on this

quiquehm
Contributor III
Contributor III
Author

Kushal, I managed something ...but still the formatting does not work correctly ...If you set Show all Values for the date_Calendar dimension ..

Show All Values.PNG

Looks like the chart shows all dates now ...but still the formatting does not show ( 25/26 March not red )

Both Jobs - All values.PNG

And when you filter by every job , the red format also goes away in many weekends ...

Bedroom-All values.PNG

Kitchen-All values.PNG

..this behaviour of the chart is really really confusing ...there must be something wrong ( or missing ) in the script I believe. Any idea ?

Thanks

Enrique

Kushal_Chawda

You need to select from job_new field which i have created. As it is obvious that in your data for those particular dates you are not having job, so when you select job that dates will not be displayed

Kushal_Chawda

As you have taken Job and Date in dimension, you should have all possible combination if Dates for each job. So you need to cross join Date with Job and then you can join it with actual data. Then this condition works properly.

Tasks:

LOAD *,

           Floor(date_Calendar) as date_key;

Load

    Job,

    Task_nr,

    Job&Task_nr    as key,

    Task_desc,

    date_start,

    date_start+IterNo()-1 as date_Calendar,

    date_finish

FROM Schedule.xlsx (ooxml, embedded labels, table is Data)

While date_start+IterNo()-1<=date_finish;

MinMax:

LOAD max(Date) as MaxDate,

           min(Date) as MinDate;

LOAD FieldValue('date_Calendar',RecNo()) as Date

AutoGenerate FieldValueCount('date_Calendar');

let vMinDate = Peek('MinDate',0,'MinMax');

let vMaxDate = Peek('MaxDate',0,'MinMax');

DROP Table MinMax;

Calendar:

LOAD *,

          WeekDay(date_key) as  Weekday_Calendar;

LOAD floor($(vMinDate)+IterNo()-1) as date_key

AutoGenerate 1

While $(vMinDate)+IterNo()-1<=$(vMaxDate);

Left Join(Calendar)

LOAD Distinct Job

Resident Tasks;

Join(Tasks)

LOAD *

Resident Calendar;

DROP Table Calendar;

quiquehm
Contributor III
Contributor III
Author

Thank you so much Kushal ! Now it works perfect . Now I will try my next challenge ...adding an INLINE table or an external excel sheet with other holiday dates , to see if I can bring them in the chart and also display them in red letters. I don´t want to abuse from your gratitude...I am still on the learning curve , this BI tool is amazing !

Many thanks again for helping out !!

Rgds

Kushal_Chawda

Glad that it was helpful. People on this community are always ready to help. This is amazing community. If you find difficulty further for holliday feel free to post your problem here you will definitely get solution.