Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Need help aggregating SUM

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

9 Replies
Not applicable

Re: Need help aggregating SUM

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).

Not applicable

Re: Need help aggregating SUM

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

Not applicable

Re: Need help aggregating SUM

In your expression try this:

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

Not applicable

Re: Need help aggregating SUM

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!

Not applicable

Re: Need help aggregating SUM

hi

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

Not applicable

Re: Need help aggregating SUM

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

Not applicable

Re: Need help aggregating SUM

Dou you can export the data in qvd file?

I need to know how the data are

Not applicable

Re: Need help aggregating SUM

I am sorry how do i do that?

Not applicable

Re: Need help aggregating SUM

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;