14 Replies Latest reply: Apr 23, 2018 6:56 AM by Sunny Talwar

# Aggregation to show a relative percentage to the total

Hello,

I have a problem with using a aggregation for constructing a relative percentage of the row value tot the total of the rowvalues.

I have uploaded a data file and a qvw file with a exeample to make it clear.
You will find some dimensions and data columns in the data file.

In the added screenshots of the app is the month apr17 selected.

The problem is that the value of the aggegation expressions is shown only on the total row or on one row of the table that is selected.

(see screenshot)

Clicking on another row makes the aggrgation appear at this row.(see screenshot ).

Regards

Wim Steskens

• ###### Re: Aggregation to show a relative percentage to the total

Try this

=Only({<Dimensie02=>}Aggr

(NODISTINCT

(

Sum({<Dimensie02=>} Data02  )/

count(DISTINCT {<Dimensie02=>} Dimensie03 )

)

*

(

sum({<Data01={"1"}, Dimensie02=>} Data04  )  /

sum({<Data01={"1"}, Dimensie02=>} Data03  )

), Dimensie03

))

• ###### Re: Aggregation to show a relative percentage to the total

Sunny,

Thank you for your help, the aggregation problem is solved.

The relative percentage is the first expression divided by the aggregation.
This works fine.

But this rises a second question.

I want to use the (sub)total on dimension02 by selecting the option "Show Partial Sums" on the tab
"Presentation" of the Chart properties,
The total is not shown (= zero).

Any suggestions, thanks again in advance.

Wim Steskens

• ###### Re: Aggregation to show a relative percentage to the total

I am not seeing 0... where are you seeing 0?

• ###### Re: Aggregation to show a relative percentage to the total

Sunny,

Apologize, it is the total on Dimension 03 (Month)

Regards,

Wim Steskens

Sibemaweg 20 / 6224 DC Maastricht

Postbus 1825   / 6201 BV Maastricht

www.leeuwenborgh.nl<http://www.leeuwenborgh.nl/>

KvK: 41074030  /  BTW: NL804223361.B02

Wim Steskens

Consulent

Tel.nr    06.15310001  /  088.0015000

E-mail:   w.steskens@leeuwenborgh.nl<mailto:w.steskens@leeuwenborgh.nl>

ROC Leeuwenborgh

Financieel Economische Zaken

Afdeling Procesmanagement en Managementinformatie (PMI)

Van: Sunny Talwar

Verzonden: Tuesday, April 17, 2018 1:06 PM

Aan: W.H.M. (Wim) Steskens <w.steskens@leeuwenborgh.nl>

Onderwerp: Re:  - Aggregation to show a relative percentage to the total

• ###### Re: Aggregation to show a relative percentage to the total

What do you want to see?

• ###### Re: Aggregation to show a relative percentage to the total

Sunny,

I wasn’t complete in my message, In my case  it only works when you select 1 Dimension03(Month).

Regards,

Wim Steskens

Van: Sunny Talwar

Verzonden: Tuesday, April 17, 2018 3:53 PM

Aan: W.H.M. (Wim) Steskens <w.steskens@leeuwenborgh.nl>

Onderwerp: Re:  - Aggregation to show a relative percentage to the total

• ###### Re: Aggregation to show a relative percentage to the total

=Sum(TOTAL <Dimensie03> Aggr

(

(

Sum({<Dimensie02=>} Data02  )/

count(DISTINCT {<Dimensie02=>} Dimensie03 )

)

*

(

sum({<Data01={"1"}, Dimensie02=>} Data04  )  /

sum({<Data01={"1"}, Dimensie02=>} Data03  )

), Dimensie03

))

• ###### Re: Aggregation to show a relative percentage to the total

Sunny,

That’s the solution for the aggregation indeed.

My goal is to have a relative percentage of the value of Expr to the value of the Aggr

I’ve put an extra expression (column) as the relative percentage in the table,

i.c the Expression divided by the Aggregation.

The total is of this Rel% is shown but this is not correct,

It has to be a % where the total of these is 100%.

According to the Total of Expr where the value is something like a weighted overage over the months.

How can I correct this ?

Regards,

Wim Steskens

Van: Sunny Talwar

Verzonden: Tuesday, April 17, 2018 4:03 PM

Aan: W.H.M. (Wim) Steskens <w.steskens@leeuwenborgh.nl>

Onderwerp: Re:  - Aggregation to show a relative percentage to the total

• ###### Re: Aggregation to show a relative percentage to the total

I am a little confused, what exactly are you trying to get? Can you share the expected output in an Excel file for me?

• ###### Re: Aggregation to show a relative percentage to the total

Sunny,

Sorry that I confused you, that wasn’t my goal. â˜º

I attached a Excell-file.

The goal is to have a pivot table like the last one in the Excell-file (background is yellow).

with celvalues per months (=dimension03 in the previous qvw-file) per Verzuimduurklasse (=dimension03 in the previous qvw-files)

The celvalue is  the relative percentage  of the value of Expr divided by Agr (colums  in the previous qvw-files).

This is not a problem.

The problem  is to also show the total of the celvalue  ( %rel) over the selected months.

Hope this makes it clear.

Regards,

Wim Steskens

Van: Sunny Talwar

Verzonden: woensdag 18 april 2018 10:58

Aan: W.H.M. (Wim) Steskens <w.steskens@leeuwenborgh.nl>

Onderwerp: Re:  - Aggregation to show a relative percentage to the total

• ###### Re: Aggregation to show a relative percentage to the total

Here you go

• ###### Re: Aggregation to show a relative percentage to the total

Sunny,

That’s it what I was looking for.

Thanks again for your great help.

Regards,

Wim Steskens

Van: Sunny Talwar

Verzonden: woensdag 18 april 2018 13:40

Aan: W.H.M. (Wim) Steskens <w.steskens@leeuwenborgh.nl>

Onderwerp: Re:  - Aggregation to show a relative percentage to the total

• ###### Re: Aggregation to show a relative percentage to the total

I am glad I was able to help.

Best,

Sunny

• ###### Re: Aggregation to show a relative percentage to the total

Thank you for your help, the aggregation problem is solved.

The relative percentage is the first expression divided by the aggregation.
This works fine.

But this rises a second question.

I want to use the (sub)total on dimension02 by selecting the option "Show Partial Sums" on the tab
"Presentation" of the Chart properties,
The total is not shown (= zero).

Any suggestions, thanks again in advance.

Wim Steskens