topic Re: Formatting based on Sum of rows in QlikView

Formatting based on Sum of rows

Thu, 12 Jan 2012 08:14:02 GMT

Hello,

Can you format an entire row based on the outcome of the sum of rows.

I have a straight table with months January to December and Each month a parameter says 1 or 0. If the total is 3 or higher, the entire row should color red...

Thanks for the help!

Steven

Formatting based on Sum of rows

Sokkorn — Thu, 12 Jan 2012 08:25:27 GMT

Hi Steven,

Suppose that your expression label for SUM(Field) is [Amount]. Then expand your expression by click on + sign ==> Select on Background Color ==> Definition : IF([Amount]>=3,Red(),White())

If this one not clear, please attached your small application to make it easy understand what you looking for.

Regards,

Sokkorn Cheav

Re: Formatting based on Sum of rows

Thu, 12 Jan 2012 08:51:26 GMT

Hey,

Thanks for the quick response. Let me clarify it a bit more...

I created a excel table because my file is rather large and more confusing than anything else...

This is the situation: I want the column Amount to be red as background when there is a value entered 3 times or more. The Amount column is a sum of several seperate lines of results. I thought I could add a column 'O?' and then if the sum of this column is 3 or higher make the column 'Amount' Red. But I have no idea how I can use the 'sum'-statement in an expression...

EDIT: chart was not shown...

Re: Formatting based on Sum of rows

Sokkorn — Thu, 12 Jan 2012 09:05:59 GMT

Hi Steven,

See the sample attached file.

HTH and let me know.

Regards,

Sokkorn Cheav

Re: Formatting based on Sum of rows

Thu, 12 Jan 2012 11:02:36 GMT

Hey

Thanks for your help but I think you misinterpret my question.

I see you entered '3' next to 23000 but thats what I meant.

What should happen is the following based on your QVW:

If the column 'Sum(amount)' is filled in 3 times or more, the whole should turn red.

I added the testfile and tried to explain it in detail there...

Thanks again for you help Sokkorn Cheav! Appreciated a lot!

Re: Formatting based on Sum of rows

Thu, 12 Jan 2012 16:05:50 GMT

I looked around and if I can change the conditional expression from VALUE = X to something more like VALUE belongs to (x,y,z,..) it should work. Is this possible?

Re: Formatting based on Sum of rows

Sokkorn — Fri, 13 Jan 2012 04:41:55 GMT

Hi seysenbrandts,

I am very poor in English. I cannot understand at all what you looking for. After look around your app still not sure what you want.

Regards,

Sokkorn Cheav

Re: Formatting based on Sum of rows

Fri, 13 Jan 2012 07:45:24 GMT

Hey

Thanks for responding. I'll try to explain it:

Situation 1: Now the column 'Amount' should be red because in 3 of the 4 quarters the Amount is higher than $ 100.000

Quarter	Amount
Q1	$ 120.000
Q2	$45.000
Q3	$101.000
Q4	$120.0000
Total	$386.000

Situation 2: Now the column 'Amount' should not be red because the condition (3 or more Quarters the amount is higher than $100.00) is not fulfilled because only 1 quarter has an amount higher than $100.000

Quarter	Amount
Q1	$55.000
Q2	$120.000
Q3	$45.000
Q4	$80.000
Total	$300.000

Hope you can help me with this (or someone else). If I just figure out how to use the sum of rows as a variable in an expression I should be able to do this...

Re: Formatting based on Sum of rows

Sokkorn — Fri, 13 Jan 2012 07:54:16 GMT

Hi seysenbrandts,

Good explain. I just understand now. I'm not sure I can deliver what you want or not in this case. Let try it together.

Regards,

Sokkorn Cheav