Displaying dimension values in a chart when there are no records for the expression to calculate

[#1]

I have two customers, one of which has some purchase and sales transactions and one which doesn't.

I would like to create a chart which sums the purchase and sales totals for each client AND displays both clients, even though one will not have any transactions to sum.

Many thanks in advance for any suggestions

[#2]

Manish,

Thanks for the script - it does indeed work will requires me to alter the source data in the load.

Currently I have one field for quantity and a description field to denote purchases and sales.

load * inline [

CLIENT_NAME,TransactionTypeCode, Quantity, Price, Amount

Company A, P, 10000, 5.00, 50000.00

Company A, S, 10000, 2.00, 20000.00

];

My current chart uses set analysis to sum the totals - (Sum({$<TransactionTypeCode={'P'}>}Quantity))

Do you know if there is a way to get the result you have offered using the data in its current form or am I best to change the load script?

Thanks again

john

[MK_QSL]

Let me have at least 4-5 rows of your data and also what you want to achieve.

At this stage I am unclear...

[#4]

clients:

LOAD * INLINE [

CLIENT_NAME

Company A

Company B

Company C   

     ];

transactions:

load * inline [

CLIENT_NAME,TransactionTypeCode,Quantity,Price, Amount

Company A,P,10000,5.00, 50000.00

Company A,S,10000,2.00, 20000.00

Company C,S,20000,2.00, 40000.00

Company C,S,5000,2.00, 10000.00

Company C,S,10000,2.00, 20000.00

];

What I'm hoping to see in my chart is a record displayed for each company, something like this:

CLIENT_NAME	Purchases	Sales	Purchase total value	Sales total value
Company A	10000	10000	50000	20000
Company B	0	0	0	0
Company C	0	35000	0	70000

[MK_QSL · Accepted solution]

Create a Straight Table

Dimension = CLIENT_NAME

Tick Suppress When Value is NULL

Tick Show All Values

Expressions

Purchase : SUM({<TransactionTypeCode = {'P'}>}Quantity)

Sales : SUM({<TransactionTypeCode = {'S'}>}Quantity)

Purchase Total Value : SUM({<TransactionTypeCode = {'P'}>}Amount)

Sales Total Value : SUM({<TransactionTypeCode = {'S'}>}Amount)

[ThornOfCrowns]

I agree that this is the method to follow, I was just in the process of typing a similar reply.

[#7]

Great - thanks very much - I'll play around with the dimension checkboxes you've highlighted and it should show me what I want.