Field as percentage of another field

[gerhardl]

Hi,

I would like to know how to create a pie chart showing one field as a percentage of another field.

I have a field called 'OTB' (which is available credit) and another field called 'Credit Limit' - I would like to know what percentage of the total credit limit for all customers (so Sum[credit limit]) is available to them.

So if the sum of all the customers' CL is 10 million, and the sum of all their Available OTB is 3 million, then I want a pie chart showing the 10 million, with 30% of it representing OTB.

[Oleg_Troyansky]

I'd create a synthetic dimension (using function ValueList) and a single expression with a condition:

If (ValueList(...) = 'OTB", sum(OTB), sum(CL) - sum(OTB) )

This way, you can show both the values and the % . By the way, in order to show both numbers on slices, clone your expression and check "Relative", and of course both expressions need to have "Values on Data Points".

cheers,

[gerhardl]

Hi Oleg,

I'm very clueless with QV.

Could you please explain to me how to create the synthetic dimension?

I create a new chart, and on the dimensions tab I select 'calculated dimension', then go to the functions tab and select Value List.... what then?

Sorry, as I said - clueless

[spividori]

Hi.

I hope I have understood the reference.

See the example.

Regards.

[gerhardl]

Sandro, I get the following message when trying to open your document:

[spividori]

Hi.

Ok, It's because you're using the Personal Edition.

In the example I have:

LOAD * INLINE [
CL, OTB, OTL
1, 100, 500
2, 60, 100
3, 30, 80
];

In the pie chart, CL as a dimension and expression: sum(OTL-OTB)/sum(all OTL-OTB) and number format: check show in %.

Hope this help!.

Regards.

[gerhardl]

Hi Sandro,

Thanks. Before I try that, could you explain to me what the load statement means and what exactly it will do.

I am loading a daily account extract with 100's of thousands of rows, and about 150 fields. I just need to know what I'm doing before I tamper with the data.

Is there no way to do this in QV itself, without touching the script?

Thanks.

[spividori]

Hi.

The load statement generated sample data to explain how to build the chart.
You could upload an example with some information from you to help you better?

Regards.

[gerhardl]

Hi,

I will do so if you think it's necessary - but my fields are pretty straight forward - it's just the two I want to use, CL and OTB, each of them containing the credit limit and OTB for the particular customer.

So can I do it without changing anything in the script?

Thanks for your patience with me - very new to all this.

Regards,

G

[spividori]

G.

"Thanks for your patience with me - very new to all this" - No problem, we try to help each other.

"So can I do it without changing anything in the script?"

Could be, but... sorry but my English is poor, so it's easier for me to understand with an example.

Regards.