Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Cbhuvi27
Contributor III
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)
1 Solution

Accepted Solutions
vincent_ardiet_
Specialist
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())

View solution in original post

24 Replies
vincent_ardiet_
Specialist
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)

Cbhuvi27
Contributor III
Contributor III
Author

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

 

vincent_ardiet_
Specialist
Specialist

yes, in your measure

Cbhuvi27
Contributor III
Contributor III
Author

Still I am unable to get the expected results

Aasir
Creator III
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.

 

vincent_ardiet_
Specialist
Specialist

Could you share the expression you used?

 

Cbhuvi27
Contributor III
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

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



Cbhuvi27
Contributor III
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..