Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
LessaBern
Contributor II
Contributor II

Aggregation in Pivot Table

Hi, I haven't worked with QV in some time, so I'm a bit rusty and hope someone here can help me. 

See attached file: My company creates budgets on a project base, but the data needs to be displayed on a project and a subcompany level. 

At the moment, on the subcompany-level, the total budget-value is always displayed on every line, as per standard QV procedure:

LessaBern_1-1624277332361.png

But now I need to divide that value by the number of subcompanies per Budget_No, so that the values in the column will add up correctly:

LessaBern_2-1624277395326.png

If I could do this in the script with the fixed number of Subcompanies per Budget_No, I would have no problems - but they want a dynamic display, where the Budget is always divided by the available/ selected number of Subcompanies, e.g. if only these five are selected, the total value still needs to add up: 

LessaBern_3-1624277754872.png

I have tried everything I could find with aggr() and p(), but I can never get QV to display the actual values I need. Does anybody have a solution? It feels like it should be really simple, but apparantly, I'm too dumb.

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

Hi,

Maybe;

if(Dimensionality()=1,sum(Budget), sum(Budget)/Count(TOTAL <[Budget Number]> Project_No))

20210621_4.png

Cheers,

Chris.

 

 

View solution in original post

4 Replies
chrismarlow
Specialist II
Specialist II

Hi,

Could you try something like;

if(Dimensionality()=1,sum(Budget), sum(Budget)/Count(TOTAL Project_No))

So;

20210621_2.png

Becomes;

20210621_3.png

Cheers,

Chris.

LessaBern
Contributor II
Contributor II
Author

Hi Chris,

thanks so much for the reply, but the problem with "total" is that it gives me the total amount of subcompanies or projects across all dimensions, and divides every budget value by it - but I need the number of (selectable/ selected) subcompanies per Budget_No:

LessaBern_0-1624300842629.png

If not for that, your solution would have worked perfectly (or my solution with p() from earlier this day). 

Thanks!

chrismarlow
Specialist II
Specialist II

Hi,

Maybe;

if(Dimensionality()=1,sum(Budget), sum(Budget)/Count(TOTAL <[Budget Number]> Project_No))

20210621_4.png

Cheers,

Chris.

 

 

View solution in original post

LessaBern
Contributor II
Contributor II
Author

Perfect, thanks! Now I feel stupid, I had completely forgotten how to aggregate a TOTAL by a dimension, this was the missing piece, thank you so much!