Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Untitled.png

1 Solution

Accepted Solutions
pedrobergo
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
pedrobergo
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

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

jananireddy
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 

brunobertels
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.

pedrobergo
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

jananireddy
Contributor

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

Thankyou Bruno for the kind help

Highlighted
jananireddy
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.

Community Browser