Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to compare the sum of two cells in an expression column with a user input value???

I was trying to compare the sum of two cells in a an expression column which is defined as(vSum=(sum(A)/1000=sum(B)/1000) and these two cells corresponds to the  'Category_1' and 'Category_2'  text in the dimension column.

Is there any way to resolve this scenario???

1 Solution

Accepted Solutions
Not applicable

Re: How to compare the sum of two cells in an expression column with a user input value???

Thank You Jagan...infarct it worked but with a little modification!!!

=if(($(vInputBox2) > $(vSum) and $(vInputBox2) > $(vSum) AND Match(A, 'Category_1', 'Category_2')),white(), 

If(($(vInputBox1) > $(vSum) AND Match(A, 'Category_1', 'Category_2')),RGB(255,155,0),

If($(vInputBox2)< $(vSum) AND Match(A, 'Category_1', 'Category_2'),RGB(255, 0, 0))))

13 Replies
MVP
MVP

Re: How to compare the sum of two cells in an expression column with a user input value???

Hi John,

Can you attach some sample file and come up with your required output.

Regadrs,

Jagan.

amit_saini
Honored Contributor III

Re: How to compare the sum of two cells in an expression column with a user input value???

John,

Did you mean something like this below???

if (sum(Category_1)=sum(Category_1), 'Yes','No') (Use as an expression)

Thanks,

AS

Not applicable

Re: Re: How to compare the sum of two cells in an expression column with a user input value???

Hi Jagan,

Thank You for ur immediate reply.

Please find attached a sample excel file with the different scenarios.

MVP
MVP

Re: How to compare the sum of two cells in an expression column with a user input value???

Hi,

Try like this

For Input 1:

=If(vInputValue1 < Sum(EstimatedHours) AND Match(A, 'Category_1', 'Category_2'), Red())

For Input2:

=If(vInputValue2 < Sum(EstimatedHours) AND Match(A, 'Category_1', 'Category_2'), RGB(255, 128, 0))

vInputValue2 and vInputValue1 are variables which holds Input box values and A is the dimension which holds 'Category_1', 'Category_2' values.

Hope this helps you.

Regards,

Jagan.

Not applicable

Re: How to compare the sum of two cells in an expression column with a user input value???

Thank you Jagan...it did work....but can we combine both these conditions as a nested if statement??

So both input values are evaluated and the background color of the 'Estimated Hours of Work cell' with respect to 'Category_1' and 'Category_2' is highlighted red and orange.

And I came across one more problem...Is there any function to sum the two cell values of  'Estimated Hours of Work with respect to 'Category_1' and 'Category_2' and compare it against the input box values??

MVP
MVP

Re: How to compare the sum of two cells in an expression column with a user input value???

Hi,

Try like this

=If(vInputValue1 < Sum(EstimatedHours) AND Match(A, 'Category_1', 'Category_2'), Red(),

If(vInputValue2 < Sum(EstimatedHours) AND Match(A, 'Category_1', 'Category_2'), RGB(255, 128, 0)))




=Sum({<A={'Category_1' , 'Category_2' }>} TOTAL EstimatedHours)


Regards,

Jagan.

Not applicable

Re: How to compare the sum of two cells in an expression column with a user input value???

The set analysis statement for summing up the row values is not working ,may be because the Column Estimated Hours calculation is assigned to a variable???

Any thoughts!!

Not applicable

Re: How to compare the sum of two cells in an expression column with a user input value???

How can we use an expression which is assigned to a variable in set analysis???@

Not applicable

Re: How to compare the sum of two cells in an expression column with a user input value???

Hi Jagan,

Your code was kind of life saver,but one more enhancement required.

vInputValue1 > Sum(EstimatedHours)

vInputValue2 >Sum(EstimatedHours)

How can I add these two condition into the same If statements.


Want to give the cell background color as default white()

Community Browser