How to aggr in a table

[#1]

Hello

I have two colomn "Account" and "amount"

I want to group account like sales= 300 - 3999, costs= 4000 - 4999 and other 5000 - 9999 in at table with group totals

like this

Account     Amount

3001          1 4522

3010          -2 69900

3011          2 99841    

Sum          1 5526

4010          13 66955

4019          119

4110          62

4520          106 922

Sum          129 223

Total          163 0022

how kan i build this with the pivot table??

Gunnar Schie

[#2]

Hi,

Quickest is to add a calculated dimension which is left(Account,1) and give it the name HeadAccount.

This will allow you to use subtotal on the Account.

Another idea is to hardcode your expressions with Set Analysis.

This will allow you total control over the total positioning of your accounts:

Sum({<Account={4010}>} Amount)

will give you the sum of all the account 4010. You do this for all your accounts.

Sum({<HeadAccount={4}>} Amount)

will give you the sum of all costing accounts, provided you have added the following to your

loading script:

left(Account,1) AS HeadAccount

If you provide me with an example document, I'll be happy to add these expressions to your document.

Kind Regards,

Dion

[#3]

Hello Dion

Thank's for anwering.

I attach a example file with some data. I wrote the group wish in the Expression for the table. I am glad if you will help me out

//Gunnar