topic Conditional formatting in QlikView

Conditional formatting

Sun, 08 May 2011 16:37:16 GMT

Hi, is it possible to have multiple charts on a tab and have the max quarter (most recent quarter) show up in a different color

For example: 2010 Q1 -2011 Q4 (all bar charts would show up in blue) and then 2012 Q1 would be in green?

Thank you

Conditional formatting

siz_mahleka — Sun, 08 May 2011 16:42:18 GMT

Hi garriguh,

Yes this is possible. Check out this video.

http://www.youtube.com/watch?v=0jCZPzh2ZAo

It should answer your question.

Regards,

Siz.

Re: Conditional formatting

Anonymous — Mon, 09 May 2011 12:02:28 GMT

Hi G.

What you can use is:

if(Quarter =Max(Total Quarter), blue())

Use this as the background colour of your expression.

Re: Conditional formatting

Mon, 09 May 2011 15:09:40 GMT

Hi< thank you for your help - but I'm think doing something wrong? Here's what my expression looks like:

=IF($(Time_Frame)=Max(=$(Time_Frame),blue(SUM([TOTAL REVENUE ($)]))/1000000

Re: Conditional formatting

Mon, 09 May 2011 16:07:09 GMT

Hello garriguh,

I think there are two things to mention:

- the color of bars within a barchart can be changed using chart props --> expression tab --> expand the "+" of the corresponding expression --> use background color definition similar to:

 if(Quarter= Max(TOTAL Quarter), Color(2))

 if(Quarter= MaxString(TOTAL Quarter), Color(2))

Which expr. will work in your case depends on the type of your period, being text or number

- in the expression definition itself use the sum() without any color, perhaps only a single sum(MyVal)/100000 or so.

Regards, Roland

Re: Conditional formatting

Mon, 09 May 2011 16:25:34 GMT

Hi Roland - thank you - I'm still unable to get this to work for me :< I'm wondering if it's because I have Time_Frame set up as a variable so that when someone chooses Quarterly they get the quarterly view vs the annual?

Conditional formatting

Mon, 09 May 2011 16:48:01 GMT

OK,

first take a look at the sytnax error --> you are closing the if() too early with your first ")"

If Time_Frame is a name of a variable and of a field you have to use in case of the variable the corresponding syntax like:

if( $(Time_Frame) = Max(Time_Frame), color(2))

BTW: it is a good habit to let variablenames start with a "v" to see it is a variable and not a field. Play around if you need the TOTAL or not.

HtH

Roland

Conditional formatting

Mon, 09 May 2011 17:19:23 GMT

Hi Roland, Thank you for your help, but I still seem to be missing something. No matter what I try and if I had total it still doesn't display my chart with a different color for max date?

Conditional formatting

Anonymous — Mon, 09 May 2011 17:22:42 GMT

Are your sales in the charts shown per Year or per Quarter?

Conditional formatting

Mon, 09 May 2011 17:24:51 GMT

they are shown both ways depending on which button they choose (Annual) or (Quarterly) which is driven by the variable (Time_Frame)

Conditional formatting

Anonymous — Mon, 09 May 2011 17:26:03 GMT

Do you also have a Field: Quarter?

Conditional formatting

Mon, 09 May 2011 17:27:34 GMT

I have a field called Fiscal Quarter and Year ex. 2012 Q1 and a field called Fiscal Year ex. 2012

Re: Conditional formatting

Anonymous — Mon, 09 May 2011 17:31:08 GMT

Ok, what happens if you use:

if(Quarter =Max(Total Quarter), blue())

Where the green Quarter is the fieldname of your Quarter

Re: Conditional formatting

Mon, 09 May 2011 17:33:09 GMT

It still doesn't change the color for me :<

Re: Conditional formatting

Anonymous — Mon, 09 May 2011 17:34:53 GMT

Can you share your document here, then I will have a look.

Re: Conditional formatting

Mon, 09 May 2011 18:22:09 GMT

Attached is an example copy of what I'm trying to do

Re: Conditional formatting

Anonymous — Mon, 09 May 2011 18:34:24 GMT

Hi G.

I found what the problem is. Your formula is right.

But your field is not a number field

I tried this:

=if([Fiscal Year] =Max(Total [Fiscal Year]), red())

And that worked.

Re: Conditional formatting

Anonymous — Mon, 09 May 2011 18:44:12 GMT

Try this in your load script

purgechar ( [Fiscal Quarter and Year],'Q' ) as Quarter

And use this field

This will drop the Q from the field so you can count with it.