Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks but that didn't work. Can you walk me through the logic for why the key is needed?
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
];
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))
=count(If(WORKFLOW_STEP_ELIG_DATE = aggr(max(WORKFLOW_STEP_ELIG_DATE), REQUEST_ID),WORKFLOW_STEP_NAME)
Hi
Please find the attached application for the logic, Let me know if this helps you
Thanks
Priyal
Mario,
I'm hoping to display a bar chart that shows B = 1, C = 1
Thanks
!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
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.