topic Re: Need help aggregating SUM in QlikView

Need help aggregating SUM

Fri, 22 Feb 2013 20:13:13 GMT

Hi-

I was hoping to find the best solution to aggregate/display values based on user selections. I am working with Reporting Tree (account hierarchy) function.

1. When user picks top level nod (2222222), values on the pivot table do not aggregate but instead report displays lines that roll under selected parent node.

My results look like this:

------56 - 250

------77 - 250

Where i would it to look like this

---22222

- 500

2. At the moment if user selects base level account, i get accurate values/# of lines. I would like to keep this option as well.

Report strucutre:

Reporting Tree Amount Sum(Amount)

11111 $1000

---22222 - 500

------56 - 250

------77 - 250

---33333 - 500

------78 - 250

------99 - 250

Please let help.

thanks

Re: Need help aggregating SUM

Fri, 22 Feb 2013 20:44:57 GMT

Hi,

How is the structure of your database?

You can show this result with your table has the structure:

DIM_1, DIM_2, DIM_3, VALUE

11111, 22222, 56, 250

11111, 22222, 77, 250

11111, 33333, 78, 250

11111, 33333, 99, 250

create a pivot table with dimensions DIM_1, DIM_2, DIM_3 and expression SUM(VALUE).

Re: Need help aggregating SUM

Fri, 22 Feb 2013 20:47:27 GMT

I am using a reporting tree for my accounts with multiple levels of parent children combination. I am defining the strucuture as part of my script.

Sample

Concatenate

LOAD Account.Lev01 as Account,

Account.Lev14

& IF(Account.Lev13<>Account.Lev14 , '/' & Account.Lev13,'')

& IF(Account.Lev12<>Account.Lev13 , '/' & Account.Lev12,'')

& IF(Account.Lev11<>Account.Lev12 , '/' & Account.Lev11,'')

& IF(Account.Lev10<>Account.Lev11 , '/' & Account.Lev10,'')

& IF(Account.Lev09<>Account.Lev10 , '/' & Account.Lev09,'')

& IF(Account.Lev08<>Account.Lev09 , '/' & Account.Lev08,'')

& IF(Account.Lev07<>Account.Lev08 , '/' & Account.Lev07,'')

& IF(Account.Lev06<>Account.Lev07 , '/' & Account.Lev06,'')

AS AccountTree

Resident Account_Raw;

Concatenate

LOAD Account.Lev01 as Account,

Account.Lev14

& IF(Account.Lev13<>Account.Lev14 , '/' & Account.Lev13,'')

& IF(Account.Lev12<>Account.Lev13 , '/' & Account.Lev12,'')

& IF(Account.Lev11<>Account.Lev12 , '/' & Account.Lev11,'')

& IF(Account.Lev10<>Account.Lev11 , '/' & Account.Lev10,'')

& IF(Account.Lev09<>Account.Lev10 , '/' & Account.Lev09,'')

& IF(Account.Lev08<>Account.Lev09 , '/' & Account.Lev08,'')

& IF(Account.Lev07<>Account.Lev08 , '/' & Account.Lev07,'')

& IF(Account.Lev06<>Account.Lev07 , '/' & Account.Lev06,'')

& IF(Account.Lev05<>Account.Lev06 , '/' & Account.Lev05,'')

ETC

Re: Need help aggregating SUM

Mon, 25 Feb 2013 13:12:22 GMT

In your expression try this:

SUM ( IF ( Account LIKE '*' & AccountTree & '*', your_value ) )

Re: Need help aggregating SUM

Mon, 25 Feb 2013 14:11:27 GMT

Hi Edurardo,

After I applied the above expression, values are no longer showing up on the report.

What else can I do?

Thank you for helping!

Re: Need help aggregating SUM

Mon, 25 Feb 2013 14:27:05 GMT

you can send your file qvw or create a test model so we can see how the words would be?

Re: Need help aggregating SUM

Mon, 25 Feb 2013 14:34:17 GMT

I can't i dont know how to disconnect data from this file, otherwise i would!

Re: Need help aggregating SUM

Mon, 25 Feb 2013 14:46:16 GMT

Dou you can export the data in qvd file?

I need to know how the data are

Re: Need help aggregating SUM

Mon, 25 Feb 2013 15:06:38 GMT

I am sorry how do i do that?

Re: Need help aggregating SUM

Mon, 25 Feb 2013 15:13:19 GMT

In your script for all tables that you create put this code

STORE * FROM YOUR_TABLE_NAME INTO YOUR_TABLE_NAME.qvd;

ex.:

TABLE_A:

LOAD ID, VALUE

FROM TEMPORARY_TABLE;

STORE * FROM TABLE_A INTO TABLE_A.qvd;