topic Re: Master calendar that isnt linked to any orderdate etc in QlikView

Master calendar that isnt linked to any orderdate etc for chart

matthewp — Tue, 02 Aug 2016 08:47:04 GMT

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;

Re: Master calendar that isnt linked to any orderdate etc

jonathandienst — Tue, 02 Aug 2016 09:00:36 GMT

And do what with it?

Re: Master calendar that isnt linked to any orderdate etc

matthewp — Tue, 02 Aug 2016 09:02:38 GMT

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

Re: Master calendar that isnt linked to any orderdate etc

vinieme12 — Tue, 02 Aug 2016 09:17:03 GMT

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

Re: Master calendar that isnt linked to any orderdate etc

jonathandienst — Tue, 02 Aug 2016 09:20:10 GMT

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.

Re: Master calendar that isnt linked to any orderdate etc

Mark_Little — Tue, 02 Aug 2016 09:21:39 GMT

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

Re: Master calendar that isnt linked to any orderdate etc

matthewp — Tue, 02 Aug 2016 09:33:07 GMT

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;

Re: Master calendar that isnt linked to any orderdate etc

sunny_talwar — Tue, 02 Aug 2016 10:49:06 GMT

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)

Re: Master calendar that isnt linked to any orderdate etc

matthewp — Tue, 02 Aug 2016 11:47:08 GMT

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)

Re: Master calendar that isnt linked to any orderdate etc

sunny_talwar — Tue, 02 Aug 2016 11:52:45 GMT

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

Re: Master calendar that isnt linked to any orderdate etc

matthewp — Tue, 02 Aug 2016 11:54:39 GMT

I mean SUM the counts, so like 1 + 1 + 1 + 1 + 1 etc per name

Re: Master calendar that isnt linked to any orderdate etc

sunny_talwar — Tue, 02 Aug 2016 11:56:07 GMT

Is this not already doing it?

=Count(DISTINCT name)

Re: Master calendar that isnt linked to any orderdate etc

matthewp — Tue, 02 Aug 2016 11:59:01 GMT

individually yes but i want a total column at the beginning like:

Re: Master calendar that isnt linked to any orderdate etc

sunny_talwar — Tue, 02 Aug 2016 12:02:44 GMT

How about this:

Sum(Aggr(Count(Distinct name), Date))

Re: Master calendar that isnt linked to any orderdate etc

matthewp — Tue, 02 Aug 2016 12:06:25 GMT

still get error in calculation with that

Re: Master calendar that isnt linked to any orderdate etc

sunny_talwar — Tue, 02 Aug 2016 12:18:41 GMT

Try this:

Re: Master calendar that isnt linked to any orderdate etc

matthewp — Tue, 02 Aug 2016 12:21:22 GMT

im using a pivot table so that is greyed out for me

Re: Master calendar that isnt linked to any orderdate etc

sunny_talwar — Tue, 02 Aug 2016 12:25:05 GMT

Here is the pivot table

Re: Master calendar that isnt linked to any orderdate etc

matthewp — Tue, 02 Aug 2016 14:37:28 GMT

When i click a persons name it only shows dates that have a 1 in them, not all dates

Re: Master calendar that isnt linked to any orderdate etc

sunny_talwar — Tue, 02 Aug 2016 14:44:01 GMT

How about now:

=Sum(Aggr(Count(DISTINCT name), Date)) + Avg({1}0)