topic Re: Qliksense Pivot table multiple measure comparison for single dimension with multiple values in App Development

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

Cbhuvi27 — Wed, 22 Nov 2023 05:42:51 GMT

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

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

vincent_ardiet_ — Wed, 22 Nov 2023 10:52:18 GMT

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)

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

Cbhuvi27 — Wed, 22 Nov 2023 13:41:53 GMT

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

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

vincent_ardiet_ — Wed, 22 Nov 2023 13:49:46 GMT

yes, in your measure

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

Cbhuvi27 — Wed, 22 Nov 2023 23:35:41 GMT

Still I am unable to get the expected results

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

Aasir — Thu, 23 Nov 2023 00:58:31 GMT

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.

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

vincent_ardiet_ — Thu, 23 Nov 2023 08:11:36 GMT

Could you share the expression you used?

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

Cbhuvi27 — Thu, 30 Nov 2023 05:24:10 GMT

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

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

vincent_ardiet_ — Thu, 30 Nov 2023 09:58:24 GMT

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

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

Cbhuvi27 — Thu, 30 Nov 2023 10:10:41 GMT

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..

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

vincent_ardiet_ — Thu, 30 Nov 2023 10:20:03 GMT

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

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

Cbhuvi27 — Thu, 30 Nov 2023 10:37:49 GMT

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.

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

Or — Thu, 30 Nov 2023 10:40:25 GMT

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)

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

vincent_ardiet_ — Thu, 30 Nov 2023 10:50:58 GMT

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)

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

Cbhuvi27 — Thu, 30 Nov 2023 11:15:37 GMT

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

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

Cbhuvi27 — Fri, 01 Dec 2023 07:32:49 GMT

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

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

Aasir — Fri, 01 Dec 2023 07:56:33 GMT

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

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

Cbhuvi27 — Fri, 01 Dec 2023 08:04:53 GMT

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

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

Cbhuvi27 — Fri, 01 Dec 2023 11:27:30 GMT

anyone can help here pls

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

vincent_ardiet_ — Fri, 01 Dec 2023 13:40:00 GMT

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: