topic Re: Aggregation in Pivot Table in QlikView

Aggregation in Pivot Table

LessaBern — Mon, 21 Jun 2021 12:50:36 GMT

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:

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:

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:

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.

Re: Aggregation in Pivot Table

chrismarlow — Mon, 21 Jun 2021 18:27:11 GMT

Hi,

Could you try something like;

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

So;

Becomes;

Cheers,

Chris.

Re: Aggregation in Pivot Table

LessaBern — Mon, 21 Jun 2021 18:44:36 GMT

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:

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

Thanks!

Re: Aggregation in Pivot Table

chrismarlow — Mon, 21 Jun 2021 18:55:55 GMT

Hi,

Maybe;

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

Cheers,

Chris.

Re: Aggregation in Pivot Table

LessaBern — Mon, 21 Jun 2021 21:43:15 GMT

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!