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

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
Author

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))))

View solution in original post

13 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi John,

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

Regadrs,

Jagan.

amit_saini
Master III
Master III

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
Author

Hi Jagan,

Thank You for ur immediate reply.

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

jagan
Luminary Alumni
Luminary Alumni

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
Author

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??

jagan
Luminary Alumni
Luminary Alumni

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
Author

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
Author

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

Not applicable
Author

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()