Master Calendar not showing all dates

[Anonymous]

I have multiple data sets which include the following fields: [ID], [Value], [Date]. Each set set corresponds to a particular day, and does not necessarily contain the same list of ID's (i.e. some days do not have any ID readings).

I would like to show all possible dates, with corresponding values (if present) for each ID on an X-axis of a chart. Currently having no luck with a Master Calendar!

Source1:

[ID] [Value] [Date]

1, 200, 01/02/2017

2, 204, 01/02/2017

3, 208, 01/02/2017

Source2:

[ID] [Value] [Date]

2, 204, 02/02/2017

3, 208, 02/02/2017

4, 202., 02/02/2017

Source3:

[ID] [Value] [Date]

1, 204, 03/02/2017

3, 220, 03/02/2017

4, 218., 03/02/2017

Output should look like this:

ID: 1

Date	Value
01/02/2017	200
02/02/2017	-
03/02/2017	204

[Gysbert_Wassenaar]

Try this expression: Sum(Value)+sum({<ID>}0). And set the Format pattern of the measure to #.##0,00;-#.##0,00;-

[marcelviegas]

Hi, Follow my general calendar below, I hope it helps you

[date]:

LOAD

Timestamp(MinDate,'DD/MM/YYYY hh:mm:ss') as MinDate,

now() as MaxDate

INLINE [

    MinDate,

    01/01/2008 00:00:00

];

STORE date into date.QVD;

DROP Table date;

//==================================================================================

DT_TAB_GERAL:

LOAD

     AutoNumber(date(AddedTimeStamp,'DD/MM/YYYY')&'|'&Hour(AddedTimeStamp)) as DT_GERAL,

     Hour(AddedTimeStamp) as [Hora],

     Date(AddedTimeStamp,'DD/MM/YYYY') as [Data],

     Month(AddedTimeStamp) as [Mês],

     Year(AddedTimeStamp) as [Ano],

     Day(AddedTimeStamp) as [Dia],

     WeekDay(AddedTimeStamp) as [Dia da Semana],

     MonthName(AddedTimeStamp) as [Período],

     if(Hour(AddedTimeStamp) > '7' and Hour(AddedTimeStamp) <= '13','Manhã',

          if(Hour(AddedTimeStamp) > '13' and Hour(AddedTimeStamp) <= '19','Tarde','Noite')) as [Turno]

      ;

LOAD Timestamp(MinDate+(IterNo()-1)/24, 'DD/MM/YYYY hh') as AddedTimeStamp

While MinDate+(IterNo()-1)/24<=MaxDate;

LOAD Floor(Min(Timestamp(MinDate,'DD/MM/YYYY hh'))) as MinDate,

     Ceil(Max(Timestamp(MaxDate,'DD/MM/YYYY hh'))) as MaxDate

From date.QVD(qvd);

STORE DT_TAB_GERAL into DT_TAB_GERAL.QVD;

DROP Table DT_TAB_GERAL;

[marcelviegas]

NOMES:

LOAD ID, NOME,DATE(DATE,'DD/MM/YYYY') AS DATE

INLINE [

     ID, NOME,DATE

    2, 'AMANDA','20/01/2016'

    1, 'MARCEL','30/01/2016'

    3 , 'MARIA','01/12/2016'

    4 , 'PEDRO','15/05/2016'

    5 , 'JOAO','25/06/2017'

    7 , 'JOAQUIM','18/01/2017'

];

TABELA_MESTRE:

LOAD

   IterNo() as Day,

   Date( DATAMIN + IterNo() - 1 ) as Date

   While DATAMIN + IterNo() - 1 <= DATAMAX;

LOAD MIN(DATE) AS DATAMIN, MAX(DATE) AS DATAMAX, Now() AS TESTEDATA

Resident NOMES;

[Anonymous]

Thanks for this solution. However, I need to count the number of ID's missing from each source file (each day) . Do you know how I could do this (ideally within the script)?

[marcelviegas]

SCRIPT  

NOMES:

LOAD ID, NOME,DATE(DATE,'DD/MM/YYYY') AS DATE

INLINE [

     ID, NOME,DATE

    2, 'AMANDA','20/01/2016'

    1, 'MARCEL','30/01/2016'

    3 , 'MARIA','01/12/2016'

    4 , 'PEDRO','15/05/2016'

    5 , 'JOAO','25/06/2017'

    7 , 'JOAQUIM','18/01/2017'

];

TMP_TABELA_MESTRE:

LOAD

   IterNo() as Day,

   Date( DATAMIN + IterNo() - 1 ) as DATE,

  MonthName(Date( DATAMIN + IterNo() - 1 )) AS PERIODO

   While DATAMIN + IterNo() - 1 <= DATAMAX;

LOAD MIN(DATE) AS DATAMIN, MAX(DATE) AS DATAMAX, Now() AS TESTEDATA

Resident NOMES;

EXPRESSÃO:

COUNT(DISTINCT DATE)-COUNT(DISTINCT{<NOME={'*'}>}DATE)