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