Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jananireddy
Partner - Creator
Partner - Creator

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

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

View solution in original post

7 Replies
pedrobergo
Employee
Employee

Hi Janani,

Try this Set Analysis.

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

[]

Pedro

brunobertels
Master
Master

Hi

May be this

Dimension Département

MEsure

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

-

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

jananireddy
Partner - Creator
Partner - Creator
Author

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

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

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
Partner - Creator
Partner - Creator
Author

Thankyou Bruno for the kind help

jananireddy
Partner - Creator
Partner - Creator
Author

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