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: 
Not applicable

Setting a status based on rows within a dimension

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!

21 Replies
Not applicable
Author

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?

t2.jpg

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Absolutely. Here they are.

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Did you manage to get this working?

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Not yet, thanks for asking.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein