5 Replies Latest reply: Jul 10, 2017 1:44 PM by marcel viegas RSS

    Master Calendar not showing all dates

    Harjit Nar

      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

      DateValue
      01/02/2017200
      02/02/2017-
      03/02/2017204
        • Re: Master Calendar not showing all dates
          Gysbert Wassenaar

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

            • Re: Master Calendar not showing all dates
              Harjit Nar

              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)?

                • Re: Master Calendar not showing all dates
                  marcel viegas

                  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)

              • Re: Master Calendar not showing all dates
                marcel viegas

                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;

                • Re: Master Calendar not showing all dates
                  marcel viegas

                  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;