Solved: Master calendar that isnt linked to any orderdate ... - Qlik Community

matthewp · ‎2016-08-02

i want to create a master calendar that isn't linked to any orderdate or any other table

Let me explain it a bit better as i have been quite ambiguous...

Data sample

name	datefrom	dateto	duration
john	21/12/2015	24/12/2015	4
john	27/01/2016	27/01/2016	1
john	12/02/2016	15/02/2016	2
john	26/02/2016	26/02/2016	1
john	10/03/2016	10/03/2016	0.5
john	01/04/2016	01/04/2016	1
john	18/04/2016	18/04/2016	0.5
john	29/04/2016	29/04/2016	1
john	02/09/2016	09/09/2016	6

Current (pivot table) output:

Current pivot table dimensions:

name

tempdate

Current pivot table expression:

IF(tempdate >= datefrom AND tempdate <= dateto, 1, 0 )

Expected output (shortened) i would like ALL dates shown not just the populated ones:

name	tempdate	19/12/2015	20/12/2015	21/12/2015	22/12/2015	23/12/2015	24/12/2015	25/12/2015	26/12/2015	27/12/2015	28/12/2015	29/12/2015	30/12/2015	31/12/2015
john		0	0	1	1	1	1	0	0	0	0	0	0	0

ALSO tempdate comes from the master calendar example on here....

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(datefrom) as minDate,

max(dateto) as maxDate

Resident newtable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS datefrom,

TempDate AS tempdate,

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;

sunny_talwar · ‎2016-08-03

Script:

Table1:

LOAD * INLINE [

enum, datefrom, dateto, duration

12, 21/12/2015, 24/12/2015, 4

12, 27/01/2016, 27/01/2016, 1

12, 12/02/2016, 15/02/2016, 2

12, 26/02/2016, 26/02/2016, 1

13, 10/03/2016, 10/03/2016, 0.5

13, 01/04/2016, 01/04/2016, 1

13, 18/04/2016, 18/04/2016, 0.5

13, 29/04/2016, 29/04/2016, 1

13, 02/09/2016, 09/09/2016, 6

];

Table2:

LOAD * INLINE [

enum, name

12, john

13, rahul

];

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load min(datefrom) as minDate,

max(dateto) as maxDate

Resident Table1;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load TempDate AS Date,

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;

Join(MasterCalendar)

LOAD DISTINCT enum

Resident Table1;

FinalMasterCalendar:

LOAD *,

Date&enum as Key

Resident MasterCalendar;

DROP Table MasterCalendar;

Join(Table1)

IntervalMatch(Date, enum)

LOAD datefrom,

dateto,

enum

Resident Table1;

Dimensions:

name

Date

Expression:

RangeSum(

Count({<Key = {"=Len(Trim(datefrom)) > 0"}>}DISTINCT name),

Avg({<Key = {"=Len(Trim(datefrom)) = 0"}>}0))

No selections

john selected

Rahul selected:

And in case you want the total to show the total number of days, you can try this:

Sum(Aggr(RangeSum(

Count({<Key = {"=Len(Trim(datefrom)) > 0"}>}DISTINCT name),

Avg({<Key = {"=Len(Trim(datefrom)) = 0"}>}0)), name, Date))

I hope this resolve all the issues

View solution in original post

jonathandienst · ‎2016-08-02

And do what with it?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

matthewp · ‎2016-08-02

create a pivot table with the expression IF(ADate >= [HA Date From] AND ADate <= [HA Date To], 1, 0 )

to put a 1 under each day

currently the tempdate from the master calendar can only link to date from so it isnt working as expected

vinieme12 · ‎2016-08-02

Why don't you add the '1' flag to your master calendar itself??

You can use set analysis to count instead of IF statement with the flag added

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

jonathandienst · ‎2016-08-02

matthewp wrote:

create a pivot table with the expression IF(ADate >= [HA Date From] AND ADate <= [HA Date To], 1, 0 )

to put a 1 under each day

currently the tempdate from the master calendar can only link to date from so it isnt working as expected

And how is the island master calender is involved in this? What would be the point of linking the master calendar to anything other than a date?

And consider Vineeth's suggestion.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Mark_Little · ‎2016-08-02

You can just load the date from you main table and alias it differently in the master calendar.

Just needs a date field and range to make the calendar. could even just make temp table for a min and max date and create it from that.

Mark

matthewp · ‎2016-08-02

Let me explain it a bit better as i have been quite ambiguous...

Data sample

name	datefrom	dateto	duration
john	21/12/2015	24/12/2015	4
john	27/01/2016	27/01/2016	1
john	12/02/2016	15/02/2016	2
john	26/02/2016	26/02/2016	1
john	10/03/2016	10/03/2016	0.5
john	01/04/2016	01/04/2016	1
john	18/04/2016	18/04/2016	0.5
john	29/04/2016	29/04/2016	1
john	02/09/2016	09/09/2016	6

Current (pivot table) output:

Current pivot table dimensions:

name

tempdate

Current pivot table expression:

IF(tempdate >= datefrom AND tempdate <= dateto, 1, 0 )

Expected output (shortened) i would like ALL dates shown not just the populated ones:

name	tempdate	19/12/2015	20/12/2015	21/12/2015	22/12/2015	23/12/2015	24/12/2015	25/12/2015	26/12/2015	27/12/2015	28/12/2015	29/12/2015	30/12/2015	31/12/2015
john		0	0	1	1	1	1	0	0	0	0	0	0	0

ALSO tempdate comes from the master calendar example on here....

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(datefrom) as minDate,

max(dateto) as maxDate

Resident newtable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS datefrom,

TempDate AS tempdate,

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;

sunny_talwar · ‎2016-08-02

Like this?

Script:

Table:

LOAD name,

datefrom,

dateto,

duration

FROM

[https://community.qlik.com/thread/227506]

(html, codepage is 1252, embedded labels, table is @1);

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load min(datefrom) as minDate,

max(dateto) as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load TempDate AS Date,

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;

Join(Table)

IntervalMatch(Date)

LOAD datefrom,

dateto

Resident Table;

Dimension:

Date

Expression

=Count(DISTINCT name)

matthewp · ‎2016-08-02

EXCELLENT!! works how i want with a bit of tweaking!

is there anyway to get a SUM column as a calculated dimension (keep getting error with SUM(DISTINCT name)

sunny_talwar · ‎2016-08-02

name is a text field, why would you want to Sum a text field? What is the goal here?

Master calendar that isnt linked to any orderdate etc for chart