Qliksense Pivot table multiple measure comparison for single dimension with multiple values

[Cbhuvi27]

Hello All,

I wanted to compare the multiple measures  of dimension and create new column with data like if all the measures are same then red else no colour.

for eg: 

Week	Date	Name1	Name2	Name3	Delta
1	1/2/2023	100	100	100
	1/3/2023	100	2	100	red()
	1/4/2023	100	100	100

Here Week and Date will be Row

Name will be column

sum(sales) would be measure

Delta is the extra column we need to create to compare measures of name1,2 and 3

[vincent_ardiet_]

Sorry, I don't get it, I don't see how you can compute the delta.
However, I don't see neither, why you wouldn't be able to get the color if you are already getting the number. 
And the easiest is to give a Label to you measure (like "Delta") and just use:
If("Delta"=0,green(),red())

[Cbhuvi27]

I mean I used Calculated dimension for Delta for now. But main problem here is I wanted to highlight if the Sum(Sales) are different.

[Or]

Assuming this is a pivot with an unknown number of Names, you'd need to do something with aggr() as a calculated dimension here. You can't have it be a measure as by definition Delta would be different than Name, meaning you would never have a full identical match.

Something like this should probably work or at least be adaptable to what you want (Note - in my case some values are null, but this shouldn't be an issue for you since you seem to always have values):

=aggr(if(Count(Distinct Aggr(Count(Dim3),Dim1,Dim2))>1,'Red'),Dim1)

[vincent_ardiet_]

Or is right.
That's why you should try to have a look to add a sub total  and compute this delta there (unless you want to be able to do a selection)

[Cbhuvi27]

We can ignore the delta part as that is the secondary requirement. I wanted to highlight the rows even if one of the SUM(Sales) are different

[Cbhuvi27]

I am using the below query to highlight but few rows are not getting highlighted in the right way

=IF((SUM(AGGR(({<Name={'A'}>} Sales),Week,Day,Sales))=SUM(AGGR(({<Name={'B'}>} Sales),Week,Day,Sales))
AND SUM(AGGR(({<Name={'B'}>} Sales),Week,Day,Sales))= SUM(AGGR(({<Name={'C'}>} Sales),Week,Day,Sales))),green(),red())

If Its different all cells of same row in red and if its same all cells in same row in green

[Aasir]

// Intermediate measures
SameSales_A = SUM({<Name={'A'}>} Sales);
SameSales_B = SUM({<Name={'B'}>} Sales);
SameSales_C = SUM({<Name={'C'}>} Sales);

// Highlight expression
=IF(
SameSales_A = SameSales_B AND SameSales_B = SameSales_C,
green(),
red()
)

[Cbhuvi27]

Sorry, could you please help with intermediate measure, means the variable ?

[Cbhuvi27]

anyone can help here pls

[vincent_ardiet_]

For example with this dataset:

Table1:
Load year(DATE) as YEAR, * inline
[
DATE,TYPE,VALUE
31-12-2022,A1,10
31-12-2022,A2,10
31-12-2022,A3,15
10-04-2023,A1,23
10-04-2023,A2,13
10-04-2023,A3,3
13-02-2023,A1,55
13-02-2023,A2,55
13-02-2023,A3,55
14-01-2023,A1,10
14-01-2023,A2,90
14-01-2023,A3,30
10-04-2022,A1,45
10-04-2022,A2,45
10-04-2022,A3,45
15-02-2023,A1,3
15-02-2023,A2,23
15-02-2023,A3,63
];

You build a pivot table with YEAR,DATE for the rows, and TYPE as a column (with sub totals).

Then you put this measure:
=If(SecondaryDimensionality()>0,Sum(VALUE)
,Min(Aggr(Sum(VALUE),TYPE,DATE,YEAR))=Max(Aggr(Sum(VALUE),TYPE,DATE,YEAR)))

And you obtain something like this: