Timeline analysis

[rodrigovdr]

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)

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

[dwforest · Accepted solution]

Use Aggr() to group by month:

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

Assuming you have a calendar as described:

Creating A Master Calendar