Announcements
cancel
Showing results for
Did you mean:
Contributor III

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

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

Labels (2)

• ### General Question

1 Solution

Accepted Solutions
Specialist

Like this
If(Min(TOTAL <DATE,YEAR> Aggr(Sum(VALUE),TYPE,DATE,YEAR))=Max(TOTAL <DATE,YEAR> Aggr(Sum(VALUE),TYPE,DATE,YEAR)),green(),red())

24 Replies
Specialist

You can modify the properties of the pivot asking for totals at Name level.
Then playing with SecondaryDimensionality() could help, like this for example:
If (SecondaryDimensionality()>0,Sum(Sales), whatever_formula_you_want_to_compute)

Contributor III
Author

Should I have this in what level, means in measures ?

Specialist

Contributor III
Author

Still I am unable to get the expected results

Creator III

Create a measure Delta

=If(Name1 = Name2 and Name2 = Name3, 'green()', 'red()')

Customize the above based on your requirements to compare and drag it to your pivot.

Specialist

Could you share the expression you used?

Contributor III
Author

IF((SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',1)))'}>} Sales)) = (SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',2)))'}>} Sales))
AND (SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',2)))'}>} Sales)) = (SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',3)))'}>} Sales)),green(),red()))

Note : I a using subfield and GetFieldSeelctions as I will be selecting the Names from filter

Specialist

And your table is a pivot? So, if you are selecting only 1 or 2 [Name] what is supposed to happen? Or with 4?
Maybe a simple table with 3 expressions for the 3 first value selected would be easier.
Before being able to give a color, are you able to display the value for the Delta column?
If you have a pivot, I guess that the measure is Sum(Sales).
If this is the case, you cannot add a new measures just for the end, that's why you have to play with SecondaryDimensionality.
Something like
If (SecondaryDimensionality()>0,Sum(Sales), RangeMax(SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',1)))'}>} Sales)),SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',2)))'}>} Sales)),SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',3)))'}>} Sales)))-RangeMin(SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',1)))'}>} Sales)),SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',2)))'}>} Sales)),SUM({<Name={'\$(=trim(SubField(GetFieldSelections(Name),',',3)))'}>} Sales))))

Contributor III
Author

Yes Correct, I am using Pivot table, I can get the delta correct and using as dimension in left. User can select till max 5 Names. We need to compare the Sales for those selections. Eg : If user select 2 names then compare SUM(Sales) for Name1 and Name 2, if user selects 3 then compare Sum(Sales) for Name1,Name2 and NAme3 etc..