topic Re: How to aggr in a table in QlikView

How to aggr in a table

Fri, 27 Sep 2013 12:53:53 GMT

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

Fri, 27 Sep 2013 15:00:13 GMT

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

Mon, 30 Sep 2013 07:58:10 GMT

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