topic Re: Count (Max(Date)) in QlikView

Count (Max(Date))

cbaqir — Wed, 07 Jan 2015 21:18:37 GMT

I have an app in which I need to display the count of the current workflow status for distinct REQUEST_ID.

Each REQUEST_ID has multiple workflow steps.

To determine current workflow status, I am looking at max(WORKFLOW_STEP_ELIG_DATE) and then want a count of the associated WORKFLOW_STEP_NAME.

I started with:
=count(aggr(max(WORKFLOW_STEP_ELIG_DATE), REQUEST_ID))

And then tried applying the FirstSortedValue expression but it didn't work because I can't use nested aggregation to get the MAX ELIG_DATE:

=FirstSortedValue([WORKFLOW_STEP_NAME], (-Aggr(Count( DISTINCT WORKFLOW_STEP_ELIG_DATE), REQUEST_ID)))

Any guidance would be greatly appreciated. Thanks in advance!

Cassandra

Re: Count (Max(Date))

saurabh5 — Wed, 07 Jan 2015 21:31:09 GMT

hi Cassandra,

here is what i think may work, create a key based on Reques_id and workflow_step_name like:

REQUEST_ID &'-'&WORKFLOW_STEP_NAME as key

now you can make a count:

=count({<WORKFLOW_STEP_ELIG_DATE={$(=max(WORKFLOW_STEP_ELIG_DATE))}>}distinct key)

-Regards

Saurabh

Re: Count (Max(Date))

cbaqir — Wed, 07 Jan 2015 22:20:11 GMT

Thanks but that didn't work. Can you walk me through the logic for why the key is needed?

Re: Count (Max(Date))

Wed, 07 Jan 2015 22:59:03 GMT

What's the output you expect, given the following data

LOAD * INLINE [

REQUEST_ID , WORKFLOW_STEP_NAME , WORKFLOW_STEP_ELIG_DATE

01, A , 10/07/2014

01, B , 11/01/2014

02, A , 01/01/2015

02,C , 01/05/2015

];

Re: Count (Max(Date))

Thu, 08 Jan 2015 00:08:32 GMT

IF this is the output based on the data shown in the Table Box, then build a chart->straight table , REQUEST_ID as dimension an as expression:

count( aggr( count (WORKFLOW_STEP_ELIG_DATE) , REQUEST_ID,WORKFLOW_STEP_NAME))

Re: Count (Max(Date))

anbu1984 — Thu, 08 Jan 2015 02:56:24 GMT

=count(If(WORKFLOW_STEP_ELIG_DATE = aggr(max(WORKFLOW_STEP_ELIG_DATE), REQUEST_ID),WORKFLOW_STEP_NAME)

Re: Count (Max(Date))

priyalshah9779 — Thu, 08 Jan 2015 05:38:19 GMT

Please find the attached application for the logic, Let me know if this helps you

Thanks

Priyal

Re: Count (Max(Date))

cbaqir — Thu, 08 Jan 2015 15:04:54 GMT

Mario,

I'm hoping to display a bar chart that shows B = 1, C = 1

Thanks

Re: Count (Max(Date))

cbaqir — Thu, 08 Jan 2015 15:09:40 GMT

!Close... but I am looking for the count of the current (max) workflow step so this is the data I am looking for:

Workflow Step Count

A 1

B 1

X 1

Re: Count (Max(Date))

cbaqir — Thu, 08 Jan 2015 15:29:40 GMT

Thanks, Priyal. The only change to this would be that I am trying to get a count of the current workflow step (distinct for each REQUEST_ID) as opposed to the count of the Request.

Re: Count (Max(Date))

cbaqir — Thu, 08 Jan 2015 16:14:04 GMT

Also, my intent is to produce a bar chart that shows Volume of Request_IDs by Current Workflow Step. If I use an aggr expression, is that limiting me to a static table of information?

Re: Count (Max(Date))

agomes1971 — Thu, 08 Jan 2015 16:15:29 GMT

Hi,

if your expression is not set analysis it is not static.

Regards

André Gomes