Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Source data for a Chart from another Straight Table

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.

1 Solution

Accepted Solutions
Jason_Michaelides
Partner - Master II
Partner - Master II

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

View solution in original post

15 Replies
Jason_Michaelides
Partner - Master II
Partner - Master II

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

Not applicable
Author

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.

Not applicable
Author

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 :

CustomerAcctNumStateGroupNumCurrencyAmt ($)
Cust1A1NYG1USD100
Cust1A2OHG1EUR-200
Cust1A3NJG1USD-50
Cust2A4NYG2USD300
Cust2A5NJG2USD-200
Cust1A6OHG3USD500

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

GroupNumCurrencyAmt ($)
G1USD50
G1EUR-200
G2USD100
G3USD500

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 :

CustomerDebitCredit
Cust1200550
Cust20100

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
Not applicable
Author

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.

Not applicable
Author

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
Partner - Master II
Partner - Master II

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
Partner - Creator III
Partner - Creator III

Hi mayank.

2) Straight Table with the following columns

CustomerDebitCredit
Cust1200550
Cust20100

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:

CustomerDebitCredit
Cust1250600
Cust2200300

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

Not applicable
Author

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.

Not applicable
Author

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.