# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## Expression to loop through a set of records for a dimension

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)

Tags (1)
1 Solution

Accepted Solutions
Highlighted
New Contributor III

## Re: Expression to loop through a set of records for a dimension

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

7 Replies
New Contributor III

## Re: Expression to loop through a set of records for a dimension

Hi Janani,

Try this Set Analysis.

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

[]

Pedro

Valued Contributor

## Re: Expression to loop through a set of records for a dimension

Hi

May be this

Dimension Département

MEsure

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

-

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

Contributor

## Re: Expression to loop through a set of records for a dimension

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

Valued Contributor

## Re: Expression to loop through a set of records for a dimension

Hi

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

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

Highlighted
New Contributor III

## Re: Expression to loop through a set of records for a dimension

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

Contributor

## Re: Expression to loop through a set of records for a dimension

Thankyou Bruno for the kind help

Contributor

## Re: Expression to loop through a set of records for a dimension

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