topic Re: How to compare Pivoted Dimension?? in QlikView

How to compare Pivoted Dimension??

soha1902 — Thu, 22 May 2014 05:29:48 GMT

Hi All,

I am Pivoting one dimension in Pivot table. For Example Dimension Name is: Custom_Value and it is having four fields like Value1, Value2, Value3 and Value4. So My pivot table looks like below:

OtherDimension	Value1	Value2	Value3	Value4
D1	V1	V1	V3	V4
D2	V11	V21	V31	V41
	V22	V22	V32	V42

Now I need to compare Value1 and Value2. If Value1 is equal to value2 then value2 cells turn green.

For this I expanded Custom_Value Dimension and under Background I wrote below expression:

IF(Custom_Value = 'Value1', (if('Value1' = 'Value2', Green()))

But it not working. And also If I write

IF(Custom_Value = 'Value1', Green()) then only header turns green.

Please help me to find this solution.

Re: How to compare Pivoted Dimension??

Thu, 22 May 2014 06:04:29 GMT

Try this

if(Custom_Value = 'Value1',(if(sum(SalesPrice)= sum({$<Custom_Value = {'Value2'}>}SalesPrice) ,rgb(179,179,179))))

Replace Sum(SalesPrice) with your expression for V1

Regards,

Chinni Kanna

Re: How to compare Pivoted Dimension??

soha1902 — Thu, 22 May 2014 06:19:44 GMT

Thanks for your reply Chinni but still only header name turns green.

Cell values are still default color..

Re: How to compare Pivoted Dimension??

tresB — Thu, 22 May 2014 06:31:39 GMT

See the attached sample if it helps. Try expression background color.

Re: How to compare Pivoted Dimension??

soha1902 — Thu, 22 May 2014 06:52:57 GMT

Thanks tresesco..

But please let me know any dynamic way where I can compare every column to other column.

For example If I am having D1,D2,D3,D4 and D5 and now want to compare D1 to D4. Then how I'll do this???

Please let me know...

Re: How to compare Pivoted Dimension??

tresB — Thu, 22 May 2014 06:56:40 GMT

See my expression. Before() could be used dynamically. If any doubt, share your sample qvw.

Re: How to compare Pivoted Dimension??

Thu, 22 May 2014 07:06:07 GMT

It should work, if not send me test file

Regards,

Chinni Kanna

Re: How to compare Pivoted Dimension??

soha1902 — Thu, 22 May 2014 07:08:01 GMT

You wrote

(Before(Sum(Amt))=Sum(Amt),Green())

But Please plz tell me if I want to compare Is D1 is equal to D3 if yes then D1 turns green.

Plz help me ......

Re: How to compare Pivoted Dimension??

tresB — Thu, 22 May 2014 07:12:35 GMT

Then, try: If(After(Sum(Amt),2)=Sum(Amt),Green())

Re: How to compare Pivoted Dimension??

Thu, 22 May 2014 08:06:02 GMT

try this

=If(CUSTOM_FIELD_NAME = 'IP Version Tag',IF(after(trim(cus_value),21)=trim(cus_value),Green()))

Regards,

Chinni Kanna