Count (Max(Date))

[cbaqir]

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

[saurabh5]

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

[cbaqir]

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

[#4]

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

];

[#5]

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

[anbu1984]

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

[priyalshah9779]

Hi

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

Thanks

Priyal

[cbaqir]

Mario,

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

Thanks

[cbaqir]

!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

[cbaqir]

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.