1 Reply Latest reply: Mar 28, 2018 4:25 PM by David Forest RSS

    Timeline analysis

    Rodrigo Viegas Dantas Rodrigues

      I'm trying to make a timeline graphic by month with dimension being the count of a "x" measure (p.number).

       

      I've already made the MasterCalendar and it works fine but I want the months fixed, so when there is no count values for a month the graphic put it to zero.

       

      I've also tried the options "Show as zeros" (on graphic appearance tab) and unchecked "Include null values".

       

      As a turnaround I created a "fixed" calendar, with all months, but I stucked when trying to separate the count "x" by month.

       

      For each "x" there is a Date associated field (p.gen_date) in which I can validate, but I would not like to create another virtual table, I'd like to filter this directly on the graphic.

       

      --------------

      Code for virtual calendar:

       

      FullCalendar:

      Let vMin = Num('2017-01-01');

      Let vMax = Num('2017-12-31');

      Load

      If(RowNo() = 1, Date($(vMin), 'YYYY-MM-DD'), Date($(vMin), 'YYYY-MM-DD') + RowNo()) as tDate

      AutoGenerate (1) While $(vMin) + RowNo() <= Date($(vMax));

       

      MonthCalendar:

      Load

      Distinct Month(tDate) as cal_Mês

      Resident FullCalendar;

       

      Drop Table FullCalendar;

       

      Sample code for measure values:

       

      generated_process:

      select

          p.number,

          p.gen_date,

          p.user,

          p.location

      from

          protocol p

      -----------------------

       

      So I've added a lines graphic and put as dimension "cal_Mês" and as measure the function below (which not worked):

      Count({$(Month(p.gen_date) = $(cal_Mês))} p.number)

       

      Sem título.png

       

      This is what I have with MasterCalendar, but when I apply some filters the months with zero values just dissapear from graphic instead of showing value equals to zero.

       

      Any ideas of solving this with a chart expression (without populating table and without creating a new virtual table)..?