topic Conditional color issue by cyclic group dimension in bar chart in QlikView

Conditional color issue by cyclic group dimension in bar chart

karthikeyan1504 — Tue, 11 Aug 2015 04:06:48 GMT

Hi all,

I am facing an issue in bar chart with conditional color based on the cyclic group dimension which has four dimension inside.

For example,

Every dimensions in the cyclic group has different values as shown below,

Values of Dimension 1 in cyclic group:

Shipping document tracking: '<5 Days', '5 to 7 Days', '>7 Days'

Values of Dimension 2 in cyclic group:

Lead time tracking: '<5 Days', '5 to 7 Days', '>7 Days'

Values of Dimension 3 in cyclic group:

Buyer Payment tracking: 'On Time', '5 to 7 Days', '>7 Days'

Values of Dimension 4 in cyclic group:

Supplier Payment tracking: 'On Time', '5 to 7 Days', '>7 Days'

Having above mentioned as scenario, I have used the below formula in expression background color,

if(([Supplier Payment Tracking]='On Time' or [Buyer Payment Tracking]='On Time' or [Shipment Document Tracking]='<5 Days' or [Lead Time Tracking]='<5 Days'), RGB(75,189,43)

,if(([Supplier Payment Tracking]='<7 Days' or [Buyer Payment Tracking]='<7 Days' or [Shipment Document Tracking]='5 to 7 Days' or [Lead Time Tracking]='5 to 7 Days'), RGB(254,187,48)

,if(([Supplier Payment Tracking]='>7 Days' or [Buyer Payment Tracking]='>7 Days' or [Shipment Document Tracking]='>7 Days' or [Lead Time Tracking]='>7 Days'), RGB(217,4,4))))

Now the issue is, I am not getting the desired color I want.

Could anyone please advise me on this?

Thanks & Regards,

Karthikeyan.

Re: Conditional color issue by cyclic group dimension in bar chart

sunny_talwar — Tue, 11 Aug 2015 04:09:30 GMT

Can you convert you bar chart into a straight table and share a screenshot?

Re: Conditional color issue by cyclic group dimension in bar chart

karthikeyan1504 — Tue, 11 Aug 2015 04:13:45 GMT

Hi Sunny,

As requested,

Re: Conditional color issue by cyclic group dimension in bar chart

jsingh71 — Tue, 11 Aug 2015 04:19:13 GMT

Can you share some sample data so we will try to implement the same.

Re: Conditional color issue by cyclic group dimension in bar chart

sunny_talwar — Tue, 11 Aug 2015 04:21:43 GMT

Keeping it on Shipment Document Tracking, can you try this:

if(([Shipment Document Tracking]='<5 Days'), RGB(75,189,43)

,if(([Shipment Document Tracking]='5 to 7 Days'), RGB(254,187,48)

,if(([Shipment Document Tracking]='>7 Days'), RGB(217,4,4))))

Re: Conditional color issue by cyclic group dimension in bar chart

karthikeyan1504 — Tue, 11 Aug 2015 04:30:45 GMT

Yes. I have tried it already...

When I keep only 'Shipment Document Tracking', it is working fine...

But it's not working for all the dimensions in cyclic group.

Re: Conditional color issue by cyclic group dimension in bar chart

sunny_talwar — Tue, 11 Aug 2015 04:31:40 GMT

If this works, then I suggest doing your if Statement a little differently:

If(GetCurrentField(GroupName) = 'Shipment Document Tracking',

If([Shipment Document Tracking]='<5 Days', RGB(75,189,43), If([Shipment Document Tracking]='5 to 7 Days', RGB(254,187,48), If([Shipment Document Tracking]='>7 Days', RGB(217,4,4)))),

If(GetCurrentField(GroupName) = 'Supplier Payment Tracking',

If([Supplier Payment Tracking]='On Time', RGB(75,189,43), If([Supplier Payment Tracking]='<7 Days', RGB(254,187,48), If([Supplier Payment Tracking]='>7 Days', RGB(217,4,4)))),

If(GetCurrentField(GroupName) = 'Buyer Payment Tracking',

If([Buyer Payment Tracking]='On Time', RGB(75,189,43), If([Buyer Payment Tracking]='<7 Days', RGB(254,187,48), If([Buyer Payment Tracking]='>7 Days', RGB(217,4,4)))),

If(GetCurrentField(GroupName) = 'Lead Time Tracking',

If([Lead Time Tracking]='<5 Days', RGB(75,189,43), If([Lead Time Tracking]='5 to 7 Days', RGB(254,187,48), If([Lead Time Tracking]='>7 Days', RGB(217,4,4))))))))

Please ignore any unintentional errors and fix them for your requirement

HTH

Best,

Sunny

Re: Conditional color issue by cyclic group dimension in bar chart

karthikeyan1504 — Tue, 11 Aug 2015 04:37:45 GMT

Hi Jsingh,

As requested,

Origin Country	Shipment Document Tracking	# of Lots
CAMBODIA	<5 Days	21
CAMBODIA	5 to 7 Days	50
CAMBODIA	>7 Days	2,911
CHINA	<5 Days	1,044
CHINA	5 to 7 Days	2,251
CHINA	>7 Days	2,158
INDIA	>7 Days	4
INDONESIA	<5 Days	3,284
INDONESIA	5 to 7 Days	1,876
INDONESIA	>7 Days	1,381
MALAYSIA	<5 Days	692
MALAYSIA	5 to 7 Days	959
MALAYSIA	>7 Days	781
MYANMAR	<5 Days	36
MYANMAR	5 to 7 Days	94
MYANMAR	>7 Days	209
VIETNAM	<5 Days	147
VIETNAM	5 to 7 Days	484
VIETNAM	>7 Days	662

Origin Country	Buyer Payment Tracking	# of Lots
CAMBODIA	On Time	46
CHINA	On Time	203
INDONESIA	On Time	31
INDONESIA	<7 Days	4
MALAYSIA	On Time	9
MALAYSIA	>7 Days	3
MYANMAR	On Time	28
VIETNAM	On Time	39

Thanks & Regards,

Karthikeyan.

Re: Conditional color issue by cyclic group dimension in bar chart

karthikeyan1504 — Tue, 11 Aug 2015 04:40:10 GMT

Hi Sunny,

How can we mention the group name instead field name like GetCurrentField(GroupName) ?

Thanks & Regards,

Karthikeyan A R.

Re: Conditional color issue by cyclic group dimension in bar chart

sunny_talwar — Tue, 11 Aug 2015 04:48:03 GMT

What is the cycle group called which includes your four fields -> Buyer Payment Tracking, Shipment Document Tracking, Supplier Payment Tracking and Lead Time Tracking?

For example the Group name is called Tracking

You can then find out which dimension you are on using GetCurrentField() function. Lets say you are on Buyer Payment Tracking then GetCurrentField(Tracking) in a text box will give you Buyer Payment Tracking and as you cycle through, this value will change.

HTH

Best,

Sunny

Re: Conditional color issue by cyclic group dimension in bar chart

sunny_talwar — Tue, 11 Aug 2015 04:49:36 GMT

If you have spaces in your Group name Like Group Name, then use the group name with square brackets -> GetCurrentField([Group Name])

Re: Conditional color issue by cyclic group dimension in bar chart

karthikeyan1504 — Tue, 11 Aug 2015 04:52:07 GMT

Hurray!!!!

I was not aware that we can mention the group name in Getcurrentfield() function, since it shows error in expression.

It worked perfectly... Thank you for your quick response with care Sunny...

Warm Regards,

Karthikeyan.

Re: Conditional color issue by cyclic group dimension in bar chart

sunny_talwar — Tue, 11 Aug 2015 04:55:25 GMT

Yes, it shows syntax error, but it works. A work around seeing a error in your expression is to create a variable (vGroupName = GetCurrentField([Group Name]) and then use the variable in your expression

If($(vGroupName) = 'Shipment Document Tracking',

If([Shipment Document Tracking]='<5 Days', RGB(75,189,43), If([Shipment Document Tracking]='5 to 7 Days', RGB(254,187,48), If([Shipment Document Tracking]='>7 Days', RGB(217,4,4)))),

If($(vGroupName) = 'Supplier Payment Tracking',

If([Supplier Payment Tracking]='On Time', RGB(75,189,43), If([Supplier Payment Tracking]='<7 Days', RGB(254,187,48), If([Supplier Payment Tracking]='>7 Days', RGB(217,4,4)))),

If($(vGroupName) = 'Buyer Payment Tracking',

If([Buyer Payment Tracking]='On Time', RGB(75,189,43), If([Buyer Payment Tracking]='<7 Days', RGB(254,187,48), If([Buyer Payment Tracking]='>7 Days', RGB(217,4,4)))),

If($(vGroupName) = 'Lead Time Tracking',

If([Lead Time Tracking]='<5 Days', RGB(75,189,43), If([Lead Time Tracking]='5 to 7 Days', RGB(254,187,48), If([Lead Time Tracking]='>7 Days', RGB(217,4,4))))))))