Source data for a Chart from another Straight Table

[#1]

Hi,

I have a straight table with calculated expressions and several dimensions. I now want to use the data from this table (only some dimensions & expressions) as input data for a bar chart and/or another table. Is this possible in QV 10 ?

Thanks.

[Jason_Michaelides]

Can't you just use the required dimensions and duplicate the expressions in your second chart?

[#3]

I think the answer to my question is the AGGR and/or TOTAL functions...I need to create an intermediate table where I would do some netting based on 3 dimensions and then use this temp table as source in another chart and table. The BIG problem is that I don't know how to use the AGGR and/or TOTAL functions.

[#4]

Sorry, the original post was made from my friend's account (edurastogi) as I was having some problems with using mine.

Here is what I am looking for :

Input data :

Customer	AcctNum	State	GroupNum	Currency	Amt ($)
Cust1	A1	NY	G1	USD	100
Cust1	A2	OH	G1	EUR	-200
Cust1	A3	NJ	G1	USD	-50
Cust2	A4	NY	G2	USD	300
Cust2	A5	NJ	G2	USD	-200
Cust1	A6	OH	G3	USD	500

Intermediate Result Expected : (sum(AMT) group by GroupNum, Currency)

GroupNum	Currency	Amt ($)
G1	USD	50
G1	EUR	-200
G2	USD	100
G3	USD	500

STATE column is available as a FILTER (list box) and the values in the Intermediate table are expected to change depending on what STATE the user selects.

Now finally I want 2 outputs :

1) Trend Chart that will show Debit and Credit amounts over time (assuming my example above is for one day's data) where Debit = Amt when less than 0 ; Credit = Amt when greater than 0.

2) Straight Table with the following columns :

Customer	Debit	Credit
Cust1	200	550
Cust2	0	100

I have attached my sample QVW here...can someone please tell me what I am doing wrong with the AGGR syntax because of which I am not getting the correct Credit/Debit values in the output table ?

Thanks.

[Anonymous]

You could potentially just do two expressions:

if(sum(Amt)>0, sum(Amt))

and

if(sum(Amt)<0, sum(Amt))

and call the expressions Debit and Credit.

[#6]

I wish it was as simple as that...however I need to first do the netting with the GroupNum, Currency combination and then aggregate the netted output at a customer level and put into DR/CR buckets depending on the sign.

[Jason_Michaelides]

I am having exactly the same challenge at work right now! Have to net at one level then aggregate that up at a different level. My problem is not understanding the concept properly i.e. I can't even write it down as I don't get it! Your explanation of the issue has helped me understand the concept a bit more - plus I am working with someone today who will hopefully make it clear for me. All this is a long-winded way of saying I hope to figure it out today so will let you know!

[magavi_framsteg]

Hi mayank.

2) Straight Table with the following columns

Customer	Debit	Credit
Cust1	200	550
Cust2	0	100

Is that the output you want?

Shouldn't Dr and Cr be currency aware?

If I do the same with you logic for Cr and Dr I get this:

Customer	Debit	Credit
Cust1	250	600
Cust2	200	300

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

[#9]

Hi Magnus,

Yes, this is the output table that I want - no currency column required - Amt/Dr/Cr are all shown in $ equivalent.

You are getting the values slightly different from the expected values because I think you are not netting by GroupNum,Currency first.

Can you please double check ?

Thanks.

[#10]

I just saw your qvw and saw that you are trying to derive the DR/CR at the input row level which is not what we want. We want the DR/CR aggregation/netting by Group Num,Currency.