Master calendar that isnt linked to any orderdate etc for chart

[matthewp]

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;

[matthewp]

but that doesnt explain why the 1 is there when it shouldnt...

[sunny_talwar]

Not sure why, but right now you have an issue with your Key field

Second row should say 01/03/201611787 instead of 29/02/201611787

[sunny_talwar]

I think the script is missing Table AA, so I won't be able to test it out unless you provide me the source data.

[matthewp]

i respectfully disagree, I think its from the calendar

i think when we create the key field:

key field = Adate + number

Adate = TempDate

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

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

minDate = min([HA Date From])

So if there is no entry for 29/02/2016 in [HA Date From] i think this is causing us issues?

[sunny_talwar]

I am not saying that we have done anything wrong, but I am just saying that there is something weird going on when we create the key (I agree that it is within the calendar). But until and unless we fix the key, you will continue to see incorrect results. Does that make sense? All I am asking is, if you can provide me access to AA table?

[matthewp]

I'll build something now,

duration

date from

date to

are already i there, what other fields do you need?

[sunny_talwar]

I just need the ability to reload this which I won't be able to do without table AA in the script and its source. It can be a simple inline table is what I all need

[matthewp]

ohh the source data table! let me make one!

Does this work

edit:

AA:

LOAD * INLINE [

Number, Name, HA Abs Duration, HA Date From, HA Date To,

11553, Peter Doe, 1, 27/01/2016 00:00:00, 27/01/2016 00:00:00

11553, Peter Doe, 2, 12/02/2016 00:00:00, 15/02/2016 00:00:00

11553, Peter Doe, 1, 26/02/2016 00:00:00, 26/02/2016 00:00:00

11787, John Smith, 1, 29/02/2016 00:00:00, 29/02/2016 00:00:00

11553, Peter Doe, 0.5, 10/03/2016 00:00:00, 10/03/2016 00:00:00

11553, Peter Doe, 1, 01/04/2016 00:00:00, 01/04/2016 00:00:00

11787, John Smith, 0.5, 15/04/2016 00:00:00, 15/04/2016 00:00:00

11553, Peter Doe, 0.5, 18/04/2016 00:00:00, 18/04/2016 00:00:00

11553, Peter Doe, 1, 29/04/2016 00:00:00, 29/04/2016 00:00:00

11787, John Smith, 1, 27/05/2016 00:00:00, 27/05/2016 00:00:00

11787, John Smith, 15, 20/06/2016 00:00:00, 08/07/2016 00:00:00

11787, John Smith, 1, 18/07/2016 00:00:00, 18/07/2016 00:00:00

11787, John Smith, 1, 05/08/2016 00:00:00, 05/08/2016 00:00:00

11553, Peter Doe, 6, 02/09/2016 00:00:00, 09/09/2016 00:00:00

];

[sunny_talwar]

Here try this change in your application:

Floor(ADate)&[Number] as Key

FinalMasterCalendar:

LOAD *,

  Floor(ADate)&[Number] as Key

Resident MasterCalendar;

[matthewp]

Excellent!

Can you explain what was happening so i can understand better for the future