Coloring a pie chart

[#1]

Group	Count
R	147
A	153
G	20

I have a straight table similar to above that I want to turn into a pie chart.

The Group column is derived from a calculated dimension that does an if statement on the difference between two dates.

The Count column is the expression, it's a simple count of rows.

I want to color the pie chart Red for the R rows, Yellow for the A rows and Green for the G rows but I can't figure out how to do it. Please can someone help?

I'm afraid I can't post my worksheet because it contains sensitive data.

[vishsaggi]

Hi Mark,

Just try this if it works.

In the Chart properties after you select the Pie chart go to Expression tab -> click on + sign then you can see background property double click and give the below expression:

= If(Group = 'R', Red(),

  If(Group = 'A', Yellow(),

  IF(Group = 'G', Green())))

Then try it out, we can change the RGB colors accordingly.

Thanks,
V.

[#3]

Hi V,

Thanks for the reply.

I have given the calculated dimension a Label of "Group" but when I try to add this in the background color expression QlikView says "bad field name(s): Group".

How do I set the calculated dimension to be called "Group"?

[vishsaggi]

Hello Mark,

I am not sure if we can do that. Cos it is a calculated dimension right. Can you do this at script level i mean giving the if condition in the script and name it as Group. If not can you send me a screen shot of that if condition you are using??

Thanks,
V.

[#5]

Hello,

I can do it in the load script but I'm trying to teach myself as much about Qlikview scripting as I can.

Here's the calculated dimension:

=if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) > 30, 'G',

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) <= 29 and Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) > 0, 'A',

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) <= 0, 'R')))

As you can see I'm comparing a due date against today's date and setting it to either R/A/G depending on how many days ahead or behind schedule it is.

[vishsaggi]

Hello Mark,

As it is a calculated dimension, we can use this if statements in the Expression tab Background color but it will be a lot of overhead using multiple if statements. I would suggest to use this if statements in side the script and then use the fields accordingly in the pie chart.

For example you are getting data from a table with fields like [Pep due date], PepNumber lets say. The script will be like

LOAD

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) > 30, 'G',

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) <= 29 and Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) > 0, 'A',

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) <= 0, 'R'))) As Group,

Pepnumber

FROM <Your table name> ;

Then use this in the pie chart.

Dimension: Group

Expression: Count(PepNumber)

[In the expression click on + sign then in the background color property give the if statement i have sent earlier].

Hope this helps. Please let me know if i have not made it clear.

Thanks,
V.

[#7]

I really appreciate your help. Going a bit off topic here... you've put "FROM <Your table name> ; at the end of your LOAD script. Should this be a temporary table or QVD?

I am loading data from a SQL table into the report.

My load script looks like this:

SQL SELECT a.duedate AS 'Pep due date' FROM dbname.dbo.tablename

What should I do to apply the LOAD script you posted after this to create the Group and Pepnumber fields?

[vishsaggi]

Hello Mark,

Try this and Let me know. Can you tell me the on which field you are using the Count() function to count the rows??

LOAD

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) > 30, 'G',

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) <= 29 and Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) > 0, 'A',

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) <= 0, 'R'))) As Group,

Pepnumber ;

SQL SELECT a.duedate AS 'Pep due date' FROM dbname.dbo.tablename;

// The Pepnumber I have used is my presumption that you have a field coming from SQL.Or is it just duedate coming from the SQL table.

Let me know.

Thanks,

V

[#9]

There's no field called Pepnumber coming from the db. Just a field called duedate that I have given the alias "Pep due date" in the load script to make it clearer for users.

On the expression I am doing:

=Count(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD'))

[vishsaggi · Accepted solution]

Ok use this for the count. So try this and let me know.

LOAD

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) > 30, 'G',

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) <= 29 and Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) > 0, 'A',

if(Round(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) <= 0, 'R'))) As Group,

Count(date((date([Pep due date], 'DD/MM/YYYY') - date(Now(), 'DD/MM/YYYY')),'DD')) As Count;

SQL SELECT a.duedate AS 'Pep due date' FROM dbname.dbo.tablename;

And try those fields in the Pie chart.

Thanks,

V.