topic Re: Add aggregation line to existing Table Box in QlikView

Add aggregation line to existing Table Box

Fri, 27 Dec 2013 15:11:55 GMT

I have an existing Table Box with some Revenue field. I would like to add one row with the total revenue over all rows in the table.

Is it possible and how ?

tks

Re: Add aggregation line to existing Table Box

swuehl — Fri, 27 Dec 2013 15:15:07 GMT

You can't use aggregation functions in a table box. Use a chart (straight table or pivot table or some other) instead.

Re: Add aggregation line to existing Table Box

Gysbert_Wassenaar — Fri, 27 Dec 2013 15:19:00 GMT

Can't be done. Use a straight table or pivot table (recommended). Or (not recommended) abuse a text object to display the aggregation result and format it nicely and line it up with the table box.

Re: Re: Add aggregation line to existing Table Box

Fri, 27 Dec 2013 15:47:51 GMT

thanks, pivot works fine.

I want the sum of revenues for all portfolios. I go to Add calculated dimension -> aggregation Sum and field Revenue -> Paste and i get the message error that is enclosed.

Could you tell me how to proceed ?

Thanks

Re: Re: Add aggregation line to existing Table Box

Gysbert_Wassenaar — Fri, 27 Dec 2013 15:58:03 GMT

Add the portfolio field as dimension, use sum(Revenue) as expression. Then on the Presentation tab select the dimension and enable the option Show Partial Sums.

Re: Re: Add aggregation line to existing Table Box

Fri, 27 Dec 2013 16:18:12 GMT

i think i am doing exactly as you describe. and i am having this error message : (

actually, i am adding the dimensions as in the snapshot i enclose. i also enclose snapshot for selection in 'add ...'

do you have an idea ?

besides, even without the aggregation, i do not get the pivot table with the dimensions as chosen in the snapshot: i get a one row one column table with 'portfolio' label. is not the appropriate way to get the pivot table ?

Re: Re: Add aggregation line to existing Table Box

Gysbert_Wassenaar — Fri, 27 Dec 2013 16:26:19 GMT

I don't think so. You mentioned a calculated dimension. That is what is causing your error. Add the field you want to aggregate over as a normal dimension. Don't add an expression as a calculated dimension. Add it as an expression on the Expressions tab.

Re: Re: Add aggregation line to existing Table Box

Fri, 27 Dec 2013 16:30:09 GMT

thanks

same error occurs when i am entering the expression in the expression tab...

Re: Re: Add aggregation line to existing Table Box

Gysbert_Wassenaar — Fri, 27 Dec 2013 16:32:45 GMT

Then you'll have to post an example document. I cannot read minds so I have no idea what you're really doing.

Re: Re: Add aggregation line to existing Table Box

Fri, 27 Dec 2013 16:33:25 GMT

i cannot rapidly since i cannot share the data. thanks anyway

Re: Re: Add aggregation line to existing Table Box

Gysbert_Wassenaar — Fri, 27 Dec 2013 16:37:23 GMT

An example doesn't necessarily mean your original document. See this document: Preparing examples for Upload - Reduction and Data Scrambling

You can use the expression editor to create the expression. On the Expression tab click on the ... (three dots) after the input field for the expression to open the Expression Editor. Then select the aggregation function from the dropdown list. Next choose the field from the Fields dropdow box. Finally click on Paste to create the expression. Click on OK to close the expression editor.

Re: Re: Add aggregation line to existing Table Box

Fri, 27 Dec 2013 16:45:44 GMT

this is giving me the error message i enclosed.

so, i tried to manually enter the expressions you suggest: Sum(Revenue) ...

I am getting a (strange) table with: in the portfolio column, nothing, a column with revenues, and a column with sum of revenues... whereas i would like to have an additional row, in the end of the table, with the sum of the revenues over all the portfolios...

is it possible?