Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist
Specialist

Count of Status at End of Month

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

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

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!

View solution in original post

10 Replies
vinafidalgo
Partner
Partner

Please check if this solution fit to you.

cbaqir
Specialist
Specialist
Author

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
Specialist II
Specialist II

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
Partner
Partner

Hi there.

See example attached.

Kind regards,

Ernesto.

cbaqir
Specialist
Specialist
Author

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
Specialist II
Specialist II

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist II
Specialist II

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!

View solution in original post