Expression to loop through a set of records for a dimension

[jananireddy]

Hello,

I need help in writing an expression to get a sum for each department and every application number in the department

Currently using a bar chart to plot this data and dimension used is department

Can we write a measure to loop through every application number for  a department and do the sum only if atleast one row has the application status approved and end stage as end

Can this be done using set analysis??

A screenshot of the data is pasted below. This is how the data will be for one iteration(1 department >> 1 application no>> all the stages it has passed)

[pedrobergo]

Hi Janani,

Try this Set Analysis.

sum({$<AppStatus={'APROVED'},EndStage={'END'} >} DateStageCompleted-DateStageAssigned)

[]

Pedro

[brunobertels]

Hi

May be this

Dimension Département

MEsure

Sum({$<AppStatus={'APPROVED'},EndStage={'END'}>}DateStageCompleted)

-

Sum({$<AppStatus={'APPROVED'},EndStage={'END'}>}DateStageAssigned)

[jananireddy]

Thankyou i will try this expression.

But i think this expression misses out one condition, It needs to sum all the 3 rows shown in the screenshot if the "ApplicationNo" has reached the end stage. On the contrary it should not sum any rows if that particular application number has not reached the end stage 

[brunobertels]

Hi

Ok in fact it's more complexe that's I was expected .

By the way I don't know how to do that.

[pedrobergo · Accepted solution]

Hi,

Can you try this?

sum( {$<ApplicationNo=P({<AppStatus={'APPROVED'},EndStage={'END'}>} ApplicationNo) >} DateStageCompleted-DateStageAssigned)

The P() function evaluate all the possible values using the condition, and return the ApplicationNo, comparing with itself. If it returns null, the first comparation isn´t true too.

It will not work on all cases, but you can use it like this table:

DeptmentOwner     ApplicationNo     Sum

[]

Pedro

[jananireddy]

Thankyou Bruno for the kind help

[jananireddy]

Thanks Pedro, Like you said this rightly work in a table and a bar chart.