Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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

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

This will work:

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

11 Replies

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

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

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

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

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

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

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

Slight change in Sunny's expression should work.

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

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

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

Not applicable

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

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

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

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

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

Is this what you want?

Capture.PNG

Expression:

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

Not applicable

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

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