How do I create a KPI pie-chart out of summarized data?

[#1]

Hello!

I am trying to create a compliance KPI pie-chart out of summarized data.

Here is the data (example):

ComputerName,Update,Computer_Installed,Computer_Applicable

PC01,MS09-001,0,1

PC01,MS09-002,1,1

PC01,MS09-003,1,1

PC01,MS09-004,0,1

Here is my attempt to create a dimension:

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 0.80, '0-79%',

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 0.90, '80-89%',

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 1, '90-100%')))

I cannot use the above script. I get error...

The measurement would be:

COUNT(DISTINCT ComputerName)

Can anyone help?

Thanks!

Robert

[sunny_talwar · Accepted solution]

Try this as your dimension

Aggr(

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 0.80, '0-79%',

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 0.90, '80-89%',

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 1, '90-100%')))

, ComputerName)

[#3]

Hi Sunny,

Thanks for your response.

This only gives me 1 value.

I would like the result to be:

0-79% - x Computers

80-89% - x computers

90-100% - x computers

I hope you understand what I mean

[sunny_talwar]

I am not sure at what level you are doing this calculation check on? Is it on the Update and ComputerName level?

Aggr(

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 0.80, '0-79%',

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 0.90, '80-89%',

if(SUM(Computer_Installed) / SUM(Computer_Applicable) <= 1, '90-100%')))

, ComputerName, Update)

May be helpful if you can provide a sample to look at

[#5]

Hi Sunny,

The last script ALMOST works

I have attached a sample so you might get an idea what I am looking for.

To the left I get the correct KPI.

To the right, I want to summarize KPI and count of computers.

In the last script I am missing one of the three KPI's...

I attached QVF file also

Thanks!

[sunny_talwar]

Seems like my first suggestion worked

[#7]

Fantastic! I tested before but did not get it to work. But now it does and just the way I wanted.

Thanks a lot Sunny!

[sunny_talwar]

Super