Calculation View get Months Dimension

[mkraus92]

Hi Community,

i am trying to create a Calculation View with the Dimension Month.

For ease of communication lets take the following example:

I have this data

I want to show the Sum of DocTotals from Invoices per Month

SELECT 
MONTH(T1."DocDate") as "Month", 
YEAR(T1."DocDate") as "Year", 
SUM(T1."DocTotal") as "Total"
FROM OINV T1
group by  YEAR(T1."DocDate"), MONTH(T1."DocDate")
order by  YEAR(T1."DocDate"), MONTH(T1."DocDate")

Now when executing this Query if there are no Invoices in February we dont get any result. Since there are no entrys in the database for February this makes sense, but not if we want to use this as an analytics tool. We want to get a result for February even if it is 0.

The only solution i see at the moment is creating a Table for months and joining on the months table.

My question now is am i overlooking something obvious? Are there any settings avaiable in the calculation views to get all months as dimension?

What i want:

What i get:

[former_member491159]

Boa tarde, eu uso a consulta abaixo,

SELECT 
'2019' as Ano,
  [1] as Jan
, [2] as Fev
, [3] as Mar
, [4] as Abr
, [5] as Mai
, [6] as Jun
, [7] as Jul
, [8] as Ago
, [9] as 'Set'
, [10] as Out
, [11] as Nov
, [12] as Dez
From
(select  sum(T1.[DocTotal]) as T, month(T1.[DocDate]) as Month from 
 OINV T1
where   year(T1.[DocDate]) = 2019 and T1.[CANCELED] = 'N'
and t1.DocTotal >0
Group by T1.[DocDate]) S
Pivot
(Sum(T) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P

[former_member225732]

Dear Manuel Kraus,

Why you can not try with case Statement.

Best Regards,

Sandesh Shinde