Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Column percentage over total with aggregated dimension

Hi everyone,

I'm trying to calculate the percentage of 2 values over the total in a pivot table. Everything works fine when using "Item Group" and "Year" as dimensions. The problem comes when trying to do the same but with an expression dimension.

We can get the percentage using something like:

SUM(SalesAmount)/SUM(TOTAL <Year> SalesAmount)

but with my aggregated dimension, this doesn't work.

I attach the QV file with my pivot table to help you understand my problem.

Thanks in advanced for your help.

1 Solution

Accepted Solutions
Not applicable

Re: Column percentage over total with aggregated dimension

Just in case anyone is interested, I got the answer. When you need to total on an aggregated dimension, the total needs to be also aggregated. Therefore, instead of using

Sum(TOTAL <Year> SalesAmount)

we need to use

Aggr(NODISTINCT Sum(TOTAL <Year> SalesAmount), Year)
7 Replies
MVP
MVP

Column percentage over total with aggregated dimension

Unfortunately, I am unable to open your attachement. It says 'Failed to open document'. Could you check your attached document, please?

It would be useful if you could state your calculated dimension in your post.

Maybe you could replace the calculated dimension with set analysis in your expressions. Probably not, since ABC classification seems to create new dimension values.

Would it be feasible to create this field in your script or does it need to be selection sensitive?

Not applicable

Re: Column percentage over total with aggregated dimension

I'll try to repost the document to see if you can open it.

About the other options you proposed-

- Creating this field in the script is not an option, because we load data from the sales table in 3 different databases, and the aggregation is done using Item Group classification, not Item Code.

- Maybe I could replace the calculated dimension with set analysis, but if possible, I have no clue about how it could be done.

The aggregated dimension is as follows:

=Aggr(

          IF(Sum(SALES.GrossProfit)/Sum(SALES.LineTotal)>=0.6, 'A',

                    IF(Sum(SALES.GrossProfit)/Sum(SALES.LineTotal)>=0.5, 'B',

                              IF(Sum(SALES.GrossProfit)/Sum(SALES.LineTotal)>=0.4, 'C',

                                        IF(Sum(SALES.GrossProfit)/Sum(SALES.LineTotal)>=0.3, 'D', 'E')

                              )

                    )

          ),

          ItemGrp,    /*This is the dimension I need to aggregate by*/

          Year         /* This is the dimension that shows also in the pivot table and the one that needs the partial totals*/

)

Not applicable

Re: Column percentage over total with aggregated dimension

I also tried to create 2 pivot tables, one using Only(Year) and a second one using Only(Year)-1, but it looks like the aggregate function doesn't work with this modifiers. Any clues?

Thanks

Not applicable

Re: Column percentage over total with aggregated dimension

Just in case anyone is interested, I got the answer. When you need to total on an aggregated dimension, the total needs to be also aggregated. Therefore, instead of using

Sum(TOTAL <Year> SalesAmount)

we need to use

Aggr(NODISTINCT Sum(TOTAL <Year> SalesAmount), Year)
Not applicable

Re: Column percentage over total with aggregated dimension

Very useful! Tks!

pieter1976
New Contributor III

Re: Column percentage over total with aggregated dimension

This worked for me.

Fact:

load * inline [Maingroup,Subgroup,value

a,a1,100

a,a1,100

a,a2,20

a,a2,20

a,a3,20

b,b1,100

b,b1,100

b,b1,100

b,b1,100

b,b2,10

b,b2,100

b,b2,100];

SUM(Value) / Aggr(NODISTINCT Sum(value), Maingroup)

4-10-2017 11-49-24.jpg

tomass_k
New Contributor

Re: Column percentage over total with aggregated dimension

Thanks, mate, this is the solution I was looking for.