Solved: loop through field on a document level - Qlik Community

Report Inappropriate Content · ‎2012-02-01

Hello. I wonder if it’s possible in QV to loop through the field on a document level (not in the script), sort of copy the functionality of banding from report editor. I’m not sure if that’s even the best approach for solving my problem.

Here’s what I need to accomplish.

For example, I have 4 regions; let’s call those regions A, B, C and D. Those regions have actual and budget data.

Let’s say that now I have a text object, and this text object has calculated background color condition. In this condition, I need to compare each region’s actual to a budget, and if actual is greater than budget in all of those regions, then color it green, if actual is less that budget in at least one of those regions, then color it red.

Rough logic would look like so:

If(sum({<Region={'A'}>}Actual)>sum({<Region={'A'}>}Budget) or

sum({<Region={'B'}>}Actual)>sum({<Region={'B'}>}Budget) or

sum({<Region={'C'}>}Actual)>sum({<Region={'C'}>}Budget) or

sum({<Region={'D'}>}Actual)>sum({<Region={'D'}>}Budget), green(),

if(…

My concern is that I can’t hard code Region values in set analysis because they are dynamic. Also, there are a lot Regions, so it’s not really feasible solution either. To make things harder, I can’t use script to do this.

Any suggestions?

Thanks!

kuba_michalik · ‎2012-02-01

I would use advanced aggregation (Aggr) for that.

If(Sum(Aggr(Sum(Actual)<Sum(Budget),Region))=0,green(),red())

It goes like this:

1/ Sum(Actual)<Sum(Budget) gets aggregated and calculated over dimension Region

2/ The resulting list of values (which would be either true of false because of the comparision) is summed up

3/ If all the results were false (Sum(Actual) was always over Sum(Budget)), the result of the outer Sum will be 0. If any of them was not false, it will be something else.

View solution in original post

kuba_michalik · ‎2012-02-01