topic Re: Setting a status based on rows within a dimension in QlikView

Setting a status based on rows within a dimension

Wed, 21 Sep 2011 19:22:41 GMT

I’m trying to figure something out that seems complicated toexplain, but I’ll give it a try. My goal is to look at a table with multipledimensions and assign “Green” and “Red” statuses at the various dimensionlevels.

Each row has a Value, and let’s say that if the number ispositive it’s Green and if it is not it is Red.

Each row has three dimensions, D1 being the highest rollupdimension, D2 the next down, and D3 being the lowest rollup dimension.

Each Dimension is scored as follows:

D3 (lowest) is green if the number of green rows is greaterthan the number of red rows.

D2 (middle) is green if the number of D3 green rows isgreater than the number of D3 red rows.

D1 (top) is green if the number of D2 green rows is greaterthan the number of D3 red rows.

So, what I’m looking for is some sort of function that tellsme the number of D3 rows in each D2, each D2 in each D1, etc.

The actual "green" and "red" aren't as important (that is, I can display these statuses in multiple ways if I wish), but I don't know how to set up a set of drill-down charts that will show me the needed Green and Red status at the various levels.

If there is a way to implement this solely through existing QV functions, that would be best, but naturally if it is primarily a scripting solution, that would be OK too.

Thanks everyone for your help!

Re: Setting a status based on rows within a dimension

jonathandienst — Wed, 21 Sep 2011 20:25:45 GMT

Let's assume that you have a straight table with D1, D2, D3 and Value as dimensions -

Expression: ValueStatus               
=If(Sum(Value) >= 0, 'Green', 'Red')
Expression: D3 status
=If(Count({<Value={">=0"}>} total <D1,D2,D3> Value) >= Count({<Value={"<0"}>} total <D1,D2,D3> Value), 'Green', 'Red')
Expression: D2 status
=If(Count({<Value={">=0"}>} total <D1,D2> Value) >= Count({<Value={"<0"}>} total <D1,D2> Value), 'Green', 'Red')
Expression: D1 status
If(Count({<Value={">=0"}>} total <D1> Value) >= Count({<Value={"<0"}>} total <D1> Value), 'Green', 'Red')

See the attached file as an example.

Hope that helps

Jonathan

Re: Setting a status based on rows within a dimension

Fri, 23 Sep 2011 13:57:31 GMT

In your sample above, do D1, D2, D3 represent the FIELD NAMES or Field Values?

Since D1 status is dependant upon the cumulative of D2s that make it up, shouldn't the last expression be:

Expression: D1 status

If(Count({<Value={">=0"}>} total <D2> Value) >= Count({<Value={"<0"}>}
      total <D2> Value), 'Green', 'Red')

Still working with this, but I need to understand these expressions. Thanks for the help!

Re: Setting a status based on rows within a dimension

jonathandienst — Fri, 23 Sep 2011 14:45:39 GMT

r316811r wrote:
In your sample above, do D1, D2, D3 represent the FIELD NAMES or Field Values?

Field names

r316811r wrote:
Since D1 status is dependant upon the cumulative of D2s that make it up, shouldn't the last expression be:
Expression: D1 status
If(Count({<Value={">=0"}>} total <D2> Value) >= Count({<Value={"<0"}>}
      total <D2> Value), 'Green', 'Red')

I don't think so - as the count of D1 records (top level of hierarchy) is the same regardless of the D2 records. The count at the top level does not matter how the lower levels are structured.This is in contrast with D3, at the lowest level of the hierarchy, where the reference is only relevant within the context of a specific D1 and D2.

Hope that helps to clarify the expressions

Jonathan

Re: Setting a status based on rows within a dimension

Fri, 23 Sep 2011 16:40:11 GMT

OK, I'm beginning to understand, but I don't think this is it, just yet. I'm going to be attaching a QV document that will show what I'm doing, but this image will help,

Notice the red and green indicators. The first column is based on the value of GOODBAD. the 2nd column should be based on the red/green status in the first column and actually seems to work. However, the third and fourth columns should be red, because the LU's have more red indicators than Green, and the Hospital column should also be red because it has more red LUs than green.

I think that the expressions you have given to me take their status based on the lowest levels (quarters) not the immedialy lower down levels.

Does this make sense?

Re: Setting a status based on rows within a dimension

jonathandienst — Sat, 24 Sep 2011 06:55:29 GMT

The third and fourth column look correct to me - you have 17 green and 15 red indicators in the first column. From your initial post I understood that to mean if there were more green than red in the first then the result should be green.

Perhaps I have not fully understood your requirement.

Jonathan

Re: Setting a status based on rows within a dimension

Mon, 26 Sep 2011 11:30:36 GMT

Thanks for your continued help.

Sorry, I have not explained it well enough. The 3rd column should be based on the 2nd column (that is, in this case, there are more red Indicaters than green in the 2nd column, so column 3 should be red); and the 4th column should be based on the 3rd column in a similar fashion.

So, in the image, you can see that the column for Indicators (column 2) looks good. Column 3 (LU's) consists of four Indicators in column 2 (IND-1, IND-2, IND-3, IND-4). IND-1 is green, but the other three are red, so LU-A should be coded as red, since there are more red Indicators than green Indicators.

Again, thanks for your patience; imagine how difficult this was getting the requirements out of the user!

Re: Setting a status based on rows within a dimension

jonathandienst — Mon, 26 Sep 2011 17:16:22 GMT

I get the problem. Have a look at the attached - I think its what you are after. I created two hidden columns to track the number of greens and reds and use the counts of the hidden columns to ensure that each level uses the previous level's green/red count, rather than the base level green/red count.

Unfortunately your test data appears not to have LU;s and hospitals that come out green, so I can't be sure that it is working, but it seems to be OK.

Regards

Jonathan

Re: Setting a status based on rows within a dimension

Tue, 27 Sep 2011 13:35:08 GMT

I assure you, I am pouring over your suggestions and cracking my brains. I'm not sure how this new version will work, since I see the following 'bad field name(s)' errors when you reference T1 and T2 in the expressions:

I see where you're going with this, but will this work, since t1 and t2 aren't fields in the table?

I attached the input data so you can play around with it...I simplified this version, just one hospital and one LU.

As always, I really appreciate your help.

Re: Setting a status based on rows within a dimension

jonathandienst — Tue, 27 Sep 2011 17:41:24 GMT

Qlikview sometimes is a little overzealous in indicating syntax errors in script/expressions and bad field names in expressions. They are 'bad' because they are not field names, but column references. You could replace t1/t2 with column(n), ir referencing the column by number rather than by name.

Jonathan

Re: Setting a status based on rows within a dimension

Tue, 27 Sep 2011 17:52:59 GMT

I guess I don't understand then. I added a couple of fields to show the left- and right- sides of the comparison within T2.

the left side is based on a count of t1's that are equal to 1, and the right side is based on the count of t1's that are equal to 0.

Both sides of the comparison show a count if '0'. Yet, in the screen print below, I can see t1's with a value of 1.

What am I not getting?

Setting a status based on rows within a dimension

jonathandienst — Tue, 27 Sep 2011 18:03:35 GMT

Not sure why its not working for you. What have you changed?

Perhaps you could post that again and I could have a look at it.

Jonathan

Re: Setting a status based on rows within a dimension

Tue, 27 Sep 2011 18:07:50 GMT

Absolutely. Here they are.

Setting a status based on rows within a dimension

jonathandienst — Tue, 27 Sep 2011 20:38:11 GMT

Sorry - had another commitment!

I have looked at the file and admit that I am stumped - I even tried with column(6) instead and the expression returns zero no matter what I try. Its late here now - perhaps it will be clearer in the morning

Jonathan

Setting a status based on rows within a dimension

jonathandienst — Wed, 28 Sep 2011 10:43:54 GMT

I played around some more with this model and I think that it may be corrupt, as it is behaving very strangely. I would try reloading the data from the source and starting again.

Jonathan

Re: Setting a status based on rows within a dimension

Wed, 28 Sep 2011 16:40:00 GMT

I took your advice and rebuilt the application from the ground up.

I am still getting the same results.

t2 - left is the left side of the comparision expression in t2

t2 - right is the right side of the t2

Setting a status based on rows within a dimension

jonathandienst — Thu, 29 Sep 2011 21:08:56 GMT

Did you manage to get this working?

Jonathan

Re: Setting a status based on rows within a dimension

Fri, 30 Sep 2011 10:56:38 GMT

Not yet, thanks for asking.

Setting a status based on rows within a dimension

jonathandienst — Sat, 01 Oct 2011 16:05:34 GMT

I realise what the problem is - you cannot use result columns in aggregation expressions - so we cannot use them in a count() statement. Qlikview returns zero from the count and this what we have been seeing.

I will work on a complete expression for each level of the hierarchy that does not use intermediate column results and will post this here shortly.

Regards

Jonathan

Re: Setting a status based on rows within a dimension

jonathandienst — Sat, 01 Oct 2011 20:08:35 GMT

Here's the solution (see attached)

Based on a hierarchty Level 3 (lowest level), Level 2 Level 1 (highest). Field status is set to Bad if the value is zero or below, Good if the status is above zero.

The expressions for each of the levels status indicators are:

Level 3:

If(Sum({<Status={'Bad'}>} RecCount) < Sum({<Status={'Good'}>} RecCount), 'qmem://<bundled>/BuiltIn/led_g.png', 'qmem://<bundled>/BuiltIn/led_r.png')

(Pretty straightfoward)

Level 2:

If(Sum(Total <Level1, Level2> Aggr(
    If(Sum({<Status={'Bad'}>} RecCount) >= Sum({<Status={'Good'}>} RecCount), -1, 1), Level1, Level2, Level3)) > 0, 
'qmem://<bundled>/BuiltIn/led_g.png', 'qmem://<bundled>/BuiltIn/led_r.png')

(Level 3 expression in Aggr, adding 1 for good and subtracting 1 for bad, and comparing the results with zero)

Level 1:

If(Sum(Total <Level1> 
    Aggr(
    If(Sum(Total <Level1, Level2> Aggr(
        If(Sum({<Status={'Bad'}>} RecCount) >= Sum({<Status={'Good'}>} RecCount), -1, 1), Level1, Level2, Level3)) > 0, 1, -1)
     Level1, Level2, Level3)) > 0, 
'qmem://<bundled>/BuiltIn/led_g.png', 'qmem://<bundled>/BuiltIn/led_r.png')

(Level 2 xpression in Aggr, adding 1 for good and subtracting 1 for bad, and comparing the results with zero)

I have tested this quite carefuly and I am confident that it works.

Regards

Jonathan