Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Compare the expression values of a single column after pivoting

Hi All,

Here is my sample data

Col1Col2Col3Col4
AQ1A1AP1
AQ1A2AP2
AQ1B1BP1
AQ1B2BP2
AQ1C1CP1
BQ1A2AP2
BQ1B1BP1
BQ1D2CP1

And , This is the output that i am looking for

Output.png

Note:

1. Col1 is pivoted.

2. Expression used = Maxstring(Col4)

Any help is appreciated.

Thanks In Advance.

Message was edited by: Ravi Kumar

Message was edited by: Ravi Kumar

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

Yeah I did this by un-checking the Suppress zero values option and it is working fine.

Any way thanks for the response.

View solution in original post

13 Replies
veidlburkhard
Creator III
Creator III

Hi Ravi,

you have to look for the duplicate values in Col3 and Col4. This is done with the expression 'Sum(Aggr(If(Count(Col4) > 1, 1, 0), Col3))'.

I implemented it in a calculated dimension, though. Which I named 'Result'. See the picture below:

PivotTest.jpg

Hope this helps

Burkhard

Clever_Anjos
Employee
Employee

Please find attached

Capturar.PNG

Not applicable
Author

Hi Burkhard,

Yes, You are right. We can get the match and no match values based on aggregation count.

The challenge here is , I could not able to apply the appropriate colors as shown in my post.

For Match -- Green

No Match -- Red

Color background expression is not working?


Do you have any thoughts on this?


Thanks for the response.

Clever_Anjos
Employee
Employee

Maybe this

Capturar.PNG

Not applicable
Author

Hi Anjos,

No this is not what i am expecting, all the Match values should be in one color and No Match values should be in other color.

Please look at the image i attached in my post for your reference.

Thanks for your response.

veidlburkhard
Creator III
Creator III

Hi Ravi,

if it is this what you want:

PivotTest.jpg

Then please use this formula for the dimension's background color:

=If(Aggr(If(Count(Col4) > 1, 1, 0), Col3) = 1, LightGreen(), LightRed())

and for the expression:

=If(Aggr(NODISTINCT If(Count({$<Col1 =>}Col4) > 1, 1, 0), Col3) = 1, LightGreen(), LightRed())

Hope this helps

Burkhard

Not applicable
Author

Hi Burkhard,

Its not working for me either in dimension or expression at least. Below is the screenshot for your reference. Please attach your file if it is working for you.

Result.png

Thanks for the response..

veidlburkhard
Creator III
Creator III

Hi Ravi,

see below the details for dimension and expression:

PivotTest Dimension.jpg

PivotTest Expression.jpg

Hope this helps

Burkhard

Not applicable
Author

Hi Burkhard,

Its working on the sample data which i have posted above, But the same I applied in my data which is more accurate and it is not working.

Could you please check the attachment once and let me know what i am missing here.

Thanks for the help.