topic loop through field on a document level in QlikView

loop through field on a document level

Wed, 01 Feb 2012 15:33:57 GMT

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!

loop through field on a document level

kuba_michalik — Wed, 01 Feb 2012 15:42:04 GMT

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.

loop through field on a document level

Wed, 01 Feb 2012 20:11:46 GMT

Nice, it didn't even cross my mind to do it that way. My requirement for the coloring has more logic in it, but I think I should be able to to make that to work based off your example. If i get stuck I will post here again. Thanks!