Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Clicking on another row makes the aggrgation appear at this row.(see screenshot ).
Any suggestions ? Thanks for your help in advance.
Regards
Wim Steskens
Try this
=Only({<Dimensie02=>}Aggr
(NODISTINCT
(
Sum({<Dimensie02=>} Data02 )/
count(DISTINCT {<Dimensie02=>} Dimensie03 )
)
*
(
sum({<Data01={"1"}, Dimensie02=>} Data04 ) /
sum({<Data01={"1"}, Dimensie02=>} Data03 )
), Dimensie03
))
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
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
I am not seeing 0... where are you seeing 0?
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
What do you want to see?
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
How about this
=Sum(TOTAL <Dimensie03> Aggr
(
(
Sum({<Dimensie02=>} Data02 )/
count(DISTINCT {<Dimensie02=>} Dimensie03 )
)
*
(
sum({<Data01={"1"}, Dimensie02=>} Data04 ) /
sum({<Data01={"1"}, Dimensie02=>} Data03 )
), Dimensie03
))
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 ?
Thanks again in advance.
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