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

Change color of 2nd dimension based on max value on 1st dimension

Hi -

Sample Data:

CategorySubCategoryPrice
C1S1100
C1S2200
C1S350
C2S11400
C2S12500
C2S13600
C3S21800
C3S22700

I need to represent the above data in a straight table and then change the background color of particular 'SubCategory' data-item to Yellow based on a clause.

The clause is the 'Subcategory' should have the highest price in its respective 'Category' section.

For example change the background color of S2, S13 and S21 to yellow since they have the highest price in their respective Category.

1 Solution

Accepted Solutions
sunny_talwar

This will work:

=If(Aggr(sum(StandardCost), EnglishProductCategoryName, EnglishProductSubCategoryName, EnglishProductName) = max(total<EnglishProductCategoryName> Aggr(Sum(StandardCost), EnglishProductCategoryName, EnglishProductSubCategoryName, EnglishProductName)), Yellow())

View solution in original post

11 Replies
sunny_talwar

May be like this:

If(PriceExpression = Max(TOTAL <Category> Aggr(PriceExpression, Category, SubCategory)), Yellow())

Here replace PriceExpression with the expression you are using for your Price column.

Not applicable
Author

It is not working. Please find the attached downloaded sample. This is what I got once I implemented the expression. From the attached file, you would find that Product categories like 'Bikes' and 'Clothing' are showing wrong result [Marked the cells with yellow background]. While Product Category 'Component' does not have anything. !

Not applicable
Author

And though I am using the expression posted by, could you please help me understand if I can use the below expression as well. It give the same result:

=If(StandardCost = max(total<EnglishProductCategoryName> StandardCost), Yellow())

Kushal_Chawda

Slight change in Sunny's expression should work.

If(PriceExpression = Max(TOTAL <Category,SubCategory> Aggr(PriceExpression, Category, SubCategory,Product)), Yellow())

sunny_talwar

Would you be able to share a qvw sample to see the issue?

Not applicable
Author

Sure. Please find the attached qvw.

As I am using Desktop Edition, so I would request you post your answer in reply (and not as an attachment). Thanks for your help in advance!

Not applicable
Author

It then produces a lot of other unwanted rows. PFA qvw file !

sunny_talwar

Is this what you want?

Capture.PNG

Expression:

=If(StandardCost = max(total<EnglishProductCategoryName, EnglishProductSubCategoryName> Aggr(Sum(StandardCost), EnglishProductCategoryName, EnglishProductSubCategoryName, EnglishProductName)), Yellow())

Not applicable
Author

No. PFA the xls. The required output has Yellow background cells.