topic Re: Best Design for Absence Planner in QlikView

Best Design for Absence Planner

Tue, 11 Dec 2012 11:52:41 GMT

Hi,

I have all our data for abscene in qlikview, and I need to design an abscence planner similar to the design below:

I know I will need to use conditional formatting for the different codes which I have and that is fine but I was wondering does anybody know the best object I could use to replicate this in qlikview? I tried a pivot chart but I couldn't seem to get the format right.

There will be 3 fields used:

Month

Day

HCode

Thanks,

Gregg

Re: Best Design for Absence Planner

Tue, 11 Dec 2012 12:04:35 GMT

you'll need to use something like this for conditional formatting in background color while writing ur expression

if(rangemin(.5,rangemax(condition),-.5)) < 0,

-Sqrt(-(rangemin(.5,rangemax(condition),-.5)))/(-.5)),

Sqrt((rangemin(.5,rangemax(condition),-.5)))/(.5))),

ARGB(255, 255, 0, 0), ARGB(255, 0, 255, 0), ARGB(255, 255, 255, 255))

Re: Best Design for Absence Planner

Tue, 11 Dec 2012 12:31:41 GMT

Hi,

Thanks for that. At the moment I am concentrating more on the design of the object for now.

I need to use a landscape table and pivot off the month as far as I can see but am struggling with this.

Regards,

Gregg

Re: Best Design for Absence Planner

Tue, 11 Dec 2012 13:14:48 GMT

Hi!

Maybe you can create a table with inline fields by day and month with a calendar and then do all the calculations via rule.

Rebeca

Re: Best Design for Absence Planner

RedSky001 — Tue, 11 Dec 2012 14:27:45 GMT

See the example attached.

Mark

Re: Best Design for Absence Planner

Tue, 11 Dec 2012 15:36:07 GMT

Thanks Mark.

Can you let me know what the following piece of code does:

load dual (WeekDay(Date),DayOfMonth) as X

Thanks,

Gregg

Re: Best Design for Absence Planner

RedSky001 — Tue, 11 Dec 2012 15:40:02 GMT

Ignore that, I meant to take that out (please mark my answer as correct if you are happy with it!)

Re: Best Design for Absence Planner

Tue, 11 Dec 2012 16:23:01 GMT

No problem!

Just another question. How did you get each day number across the “D” expression?

I have a calendar table with Week Day but I can’t get it to repeat the days across the chart as you do:

My Calendar code is:

LET varMinDate = Num('01/01/2012');
LET varMaxDate = Num(today());
LET varToDay = Num(today());

//************** Temporary Calendar *************

TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() -1) AS TempDate
AutoGenerate $(varMaxDate) - $(varMinDate) + 1;

MasterCalendar:
LOAD TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
WeekDay(TempDate) AS WeekDay,
'Q' & Ceil(Month(TempDate) / 3 ) AS Quarter,
Date(MonthStart(TempDate), 'MMM-YYYY') AS MonthYear,
num(Week(TempDate),'00')&'-'&WeekYear(TempDate) AS WeekYear,
InYearToDate(TempDate,$(varToDay),0)* -1 AS CurYTDFlag,
InYearToDate(TempDate,$(varToDay),-1)* -1 AS LastYTDFlag

Resident TempCalendar
ORDER BY TempDate ASC;

DROP Table TempCalendar;

Thanks,

Gregg

Re: Best Design for Absence Planner

RedSky001 — Tue, 11 Dec 2012 16:30:28 GMT

Make sure your chart is a povot table.

then click "Day" heading and drag it

See the attachments (Can't embed the images for some reason)

(Also I just noticed you have day number as a dimension, you don't need that you need the week number for the month)

This was on the document I already uploaded, but here it is again..

=if(month(weekend(Date))= month(Date),div(day(WeekEnd(Date)),7),div(day(Weekend(Date,-1)),7)+1)+1

Re: Best Design for Absence Planner

Tue, 11 Dec 2012 16:43:02 GMT

Thanks Mark Got it now.

Regards,

Gregg