Count of Status at End of Month

[cbaqir]

I have a requirement to show a "snapshot" count by status for each month (as it stands at the end of the month).

For example, Request 01 was Created in October, Open at the end of November, Open at the BEGINNING of December but then Closed by the End of December.

I want my counts to show Created - October, Open - November and Closed - December since the request was not opened at the end of the month. How can I get it to ignore the Open if the status is Closed or Cancelled in the same month?

Please see attached example. Thanks in advance!

Cassandra

[vinafidalgo]

Please check if this solution fit to you.

[cbaqir]

In theory, yes but in our use, no. We created STEP_STATUS to map all of the many possibilities of the WORKFLOW_STEP_STATUS. We would basically have to say:

=count(DISTINCT{<[STEP_STATUS]= {'Open'},WORKFLOW_STEP_NAME={'OpenA','OpenB', 'OpenC','OpenD','OpenE','OpenF',.....}>} REQUEST_ID)

What I am looking for is a way to ignore the open count if the request is open in the same month it is closed or cancelled.

As you can see if you look at REQUEST_ID 02 or 03, we are still getting Open and Closed counts in the same month.

[jerem1234]

You can try to use just an if statement:

if(count(DISTINCT{<[STEP_STATUS]= {'Closed'}>} REQUEST_ID)=0 and count(DISTINCT{<[STEP_STATUS]= {'Cancelled'}>} REQUEST_ID)=0,

count(DISTINCT{<[STEP_STATUS]= {'Open'}>} REQUEST_ID)

)

Hope this helps!

[consenit]

Hi there.

See example attached.

Kind regards,

Ernesto.

[cbaqir]

Jeremiah,

Thanks. What syntax would I use to have it ignore a STEP_STATUS of Closed if a Request was Cancelled? The workflow requires a request to be closed before it is cancelled but we only want to report on it being cancelled.

Similarly, if a request was Created and Closed in the same month but went through several workflow steps thereby creating several 'Open' steps, we would only want to see Created and Closed in the counts for the month.

Is that too complex for QV scripting and I should have it done in the ETL?

Thanks again!

[jerem1234]

You can try using this expression for Open, to deal with if created and closed happen in same month:

if((count(DISTINCT{<[STEP_STATUS]= {'Closed'}>} REQUEST_ID)=0 and count(DISTINCT{<[STEP_STATUS]= {'Cancelled'}>} REQUEST_ID)=0) and not

(count(DISTINCT{<[STEP_STATUS]= {'Closed'}>} REQUEST_ID)>=1 and count(DISTINCT{<[STEP_STATUS]= {'Create'}>} REQUEST_ID)>=1),

count(DISTINCT{<[STEP_STATUS]= {'Open'}>} REQUEST_ID)

)

And then use this expression for closed, dealing if cancelled happened in the same month:

if(count(DISTINCT{<[STEP_STATUS]= {'Cancelled'}>} REQUEST_ID)=0,

count(DISTINCT{<[STEP_STATUS]= {'Closed'}>} REQUEST_ID)

)

As long as you don't see performance taking a toll when looking at the chart and dashboard, then it should be fine to use these expressions . If you see a performance issue, then you might have to think about doing some stuff in the script.

Hope this helps!

[cbaqir]

Jeremiah,

I understand the syntax but it's not working when I implement it. I'll have to dig into the expression to see if I can figure out why.

Thanks though!

Regards,

Cassandra

[cbaqir]

How does the syntax call out the month of the TRX_DATE? If the Request has a STEP_STATUS of Closed, Cancelled or On Hold in month A, then the Open status should not be counted (since it is counted in Closed, for example).

[jerem1234 · Accepted solution]

Changed the expression a bit to calculate on the level of ID and Month. Also had to add the month to script, like:

LOAD *, month(TRX_DATE) as TRX_MONTH INLINE [

REQUEST_ID  , WORKFLOW_STEP_NAME , STEP_STATUS, TRX_DATE

01, Create, Create, 10/07/2014

01, OpenA, Open, 11/01/2014

01, OpenB, Open, 11/15/2014

01, OpenC, Open, 12/15/2014

01, Closed, Closed, 12/22/2014

02, Create, Create, 12/01/2014

02, OpenA, Open, 12/03/2014

02, OpenB, Open, 12/15/2014

02, Closed, Closed, 12/22/2014

02, Cancelled, Cancelled, 12/22/2014

03, Create, Create, 12/01/2014

03, OpenA, Open, 12/03/2014

03, Closed, Closed, 12/15/2014

];

Then I changed the expression to be surround by and aggr and sum like:

sum(aggr(if((count(DISTINCT{<[STEP_STATUS]= {'Closed'}>} REQUEST_ID)=0 and count(DISTINCT{<[STEP_STATUS]= {'Cancelled'}>} REQUEST_ID)=0) and not

(count(DISTINCT{<[STEP_STATUS]= {'Closed'}>} REQUEST_ID)>=1 and count(DISTINCT{<[STEP_STATUS]= {'Create'}>} REQUEST_ID)>=1),

count(DISTINCT{<[STEP_STATUS]= {'Open'}>} REQUEST_ID)

), REQUEST_ID, TRX_MONTH))

Please find attached as well.

Hope this helps!