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

NicoLavio

Hello all,

I am stuck on the coding of formula and I need your talent.

Objective is to count the number of “Issues” that are in status “open” at the end of each month:

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”

…

My first idea was to develop a formula looking like

CASE

WHEN "Year and Month" = '2017 / 01' THEN

COUNT (DISTINCT

CASE WHEN “Issue Open Date” < timestamp '2017-02-01 00:00:01' AND “Issue Completed date” >= timestamp '2017-02-01 00:00:01' THEN “Issue_ID” ELSE NULL END)

WHEN "Year and Month" = '2017 / 02' THEN

                        …

but:

- it leaves to a very complicate formula (reviewing month by month).

- it’s not working at all. One if the issues is that each “Issue_ID” is counted only one time. In example, an Issue opened before Jan 2017 and closed in 2018 will appear only in the count of Jan 2017, but not in the next months.

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”

Does one of you have any idea about a successful way to code this count?

Many thanks in advance for any suggestion or comment?

Nico

Gianni Ceresa

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