cancel
Showing results for
Did you mean:
Not applicable

## Compare the expression values of a single column after pivoting

Hi All,

Here is my sample data

 Col1 Col2 Col3 Col4 A Q1 A1 AP1 A Q1 A2 AP2 A Q1 B1 BP1 A Q1 B2 BP2 A Q1 C1 CP1 B Q1 A2 AP2 B Q1 B1 BP1 B Q1 D2 CP1

And , This is the output that i am looking for

Note:

1. Col1 is pivoted.

2. Expression used = Maxstring(Col4)

Any help is appreciated.

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.

13 Replies
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:

Hope this helps

Burkhard

Employee

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.

Employee

Maybe this

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.

Creator III

Hi Ravi,

if it is this what you want:

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.

Thanks for the response..

Creator III

Hi Ravi,

see below the details for dimension and expression:

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.

Community Browser