SQL Formula to count the # of uncompleted issues at the end of each month.

Hi,

First of all this kind of count isn't something you are supposed to code, but something you model in the RPD and it will work for all your months all the time without any complex formula in the front end.

NicoLavio wrote:

 

I have the following subject areas:

About issues:

“Issue_ID”

“Issue Open Date”

“Issue Completed date”

“Issue Status” (to make my example easier I will consider only closed issues)

 

I also have subject areas dedicated to time (named “all propose dates”)

“Date”

“Week”

“Year and Month”

…

I imagine you mean you have those presentation columns, which are part of one or many presentation tables and all inside the same subject area?

NicoLavio wrote:

 

I tried a second method with a formula looking like:

 

(CASE WHEN "Issue Open Date" <= TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH("Year and Month") * -(1) + 1, "Year and Month")) /* Formula for last day of the month */

AND "Issue Completed date" >= TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH("Year and Month") * -(1) + 1, "Year and Month")))

THEN COUNT(DISTINCT "Issue_ID") END)

 

Here I have an error message because “Function Calendar Extract is called with an incompatible type”

Isn't "Year and Month" just a varchar? Data types matter: you said you have "Date", "Week" and "Year and Month". I suspect "Date" is a proper date data type, while "Week" and "Year and Month" are varchar (or at the best numbers like 2019.01 etc.). These are levels being above the "Date", in your time dimension you can have a real date column representing either the beginning of those upper levels or the end. So if you have for "Year and Month" values like "2019-07", it's practical to have next to it in the time dimensions 2 columns being 2019-07-01 and 2019-07-31 (if dates or with 00:00:00 and 23:59:59 if datetime / timestamps). In this way you could easily have logical operations like BETWEEN or <= and > to do calculations on time.

A possible way to get the counting could be COUNT(DISTINCT CASE WHEN <the column representing the end of the month> BETWEEN "Issue Open Date" AND "Issue Close Date" THEN "Issue_ID" END) .