topic Re: Source data for a Chart from another Straight Table in QlikView

Source data for a Chart from another Straight Table

Tue, 10 Jul 2012 18:26:22 GMT

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.

Re: Source data for a Chart from another Straight Table

Jason_Michaelides — Tue, 10 Jul 2012 19:54:32 GMT

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

Re: Source data for a Chart from another Straight Table

Tue, 10 Jul 2012 20:55:29 GMT

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.

Re: Source data for a Chart from another Straight Table

Tue, 10 Jul 2012 21:30:39 GMT

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.

Re: Source data for a Chart from another Straight Table

Anonymous — Wed, 11 Jul 2012 04:11:05 GMT

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.

Re: Source data for a Chart from another Straight Table

Wed, 11 Jul 2012 04:29:58 GMT

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.

Re: Source data for a Chart from another Straight Table

Jason_Michaelides — Wed, 11 Jul 2012 07:11:49 GMT

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!

Re: Source data for a Chart from another Straight Table

magavi_framsteg — Wed, 11 Jul 2012 08:37:11 GMT

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.

Re: Source data for a Chart from another Straight Table

Wed, 11 Jul 2012 12:44:16 GMT

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.

Re: Source data for a Chart from another Straight Table

Wed, 11 Jul 2012 12:48:15 GMT

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.

Re: Source data for a Chart from another Straight Table

magavi_framsteg — Wed, 11 Jul 2012 14:20:45 GMT

Hi again mayank.

I don't get it.

How do you derive this result from the input table - what is the logic?

Customer	Debit	Credit
Cust1	200	550
Cust2	0	100

Do you have to select the state first?

And currency? Group?

For me, it doesn't mean anything.

Ok Cust1 has 200d and 550c. In Total? Per group? Per currency or what?

I mean... when is it you want the table to result like that?

I get it that Cust1 has 200d for group G1 and currency EUR.

But the table doesn't show that to me. It looks like Cust1 has 200d in total.

Cust1 550c I could never derive....

Re: Source data for a Chart from another Straight Table

Wed, 11 Jul 2012 16:11:40 GMT

The final output table is really an aggregation of the AMT data at a Customer level using the Intermediate result. During aggregation, +ve values go to CR and -ve go to the DR buckets.

Intermediate result is a netting of the AMT by GroupNum, Currency.

I need to figure out the right sytax for the AGGR (or TOTAL) function that can get me the desired intermediate result.

Re: Source data for a Chart from another Straight Table

Jason_Michaelides — Wed, 11 Jul 2012 17:37:15 GMT

Hi Mayank,

After a lot of fiddling I got this sorted for my own, similar challenge today. When I get home I'll look at your app and see of the same logic works. You don't need TOTAL, just Aggr()

Jason

Re: Source data for a Chart from another Straight Table

Jason_Michaelides — Wed, 11 Jul 2012 22:17:13 GMT

Here you go:

Credit:

Sum(Aggr(if(sum(Amt) >= 0,sum(Amt),0),GroupNum,Currency))

Debit:

Sum(Aggr(if(sum(Amt) < 0,sum(Amt),0),GroupNum,Currency))

Hope this helps,

Jason

Re: Source data for a Chart from another Straight Table

Thu, 12 Jul 2012 01:49:08 GMT

Worked as expected. Thanks.

Re: Source data for a Chart from another Straight Table

Jason_Michaelides — Thu, 12 Jul 2012 06:31:12 GMT

Cool. Please mark the correct answer to close the thread.