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]

Yeh its a different approach to it for sure!

One more thing, the total column isnt adding the 0.5 duration days as it should.

to handle this in the expression i use:

IF(duration = 0.5, (Sum(Aggr(RangeSum(

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

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

Sum(Aggr(RangeSum(

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

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

which works for the main data but not for the total column (show partial sums)

[matthewp]

are partial sums unavailable to do decimals?

[sunny_talwar]

Trying it out now

[sunny_talwar]

Try this out:

Sum(Aggr(IF(duration = 0.5, (RangeSum(

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

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

RangeSum(

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

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

[sunny_talwar]

And why not just this

Sum(Aggr(IF(duration = 0.5, 0.5,

RangeSum(

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

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

[matthewp]

Brilliant!

[matthewp]

Getting a strange issue with one of them,

i am getting a value of 1 in a box but when i look at the data in a table there is no entry for that date

[sunny_talwar]

Would you be able to share a sample of where you are seeing this

[matthewp]

If you look at 1st of march there is an entry for john smith that shouldnt be and disappears if you select the month march

[sunny_talwar]

John Smith is still 0, but just showing because we are not suppressing zero values in the chart. I guess the issue is that we have not 'Suppress Zero Value' on the presentation tab. If we do that, then we loose all zeros from regular view which is not what you would want.