topic Re: set analysis to calculate a double weighted average in App Development

set analysis to calculate a double weighted average

cocuzzan — Tue, 30 Oct 2018 08:35:53 GMT

Hello
I have to calculate a weighted average to appear in the Total of a table.

The difficulty lies in the fact that it is the weighted average of a weighted average and I have to calculate it directly in a table through the set analysis.

Let me explain better, in the cells of the row must appear the value of the first weighted average, but in the total must appear the weighted average of the average calculated in the individual cells.

I was thinking of making a nested sum but I can not get over it.
I enclose a sample table of the calculation that I have to do.

In practice I have a Dimension for each row of which I calculate the hourly revenue of the hours through heavy average of the records present. and this value goes into the cells of the respective rows. For each row for I have another column, always hours but with other meaning that I need in turn to calculate the total according to the weighted average of the calculated values precedents.

I enclose an example image. of which the value I expect is € 41.80
Thanks in advance

Re: set analysis to calculate a double weighted average

sunny_talwar — Tue, 30 Oct 2018 10:57:10 GMT

Would you be able to share a sample to check this out?

Re: set analysis to calculate a double weighted average

cocuzzan — Tue, 30 Oct 2018 10:59:38 GMT

no, but your have a simply example in jpg. i am sorry

Re: set analysis to calculate a double weighted average

sunny_talwar — Tue, 30 Oct 2018 11:02:56 GMT

Your simple example doesn't even specify what your dimensions are and what is your expression. I guess you need to look here

Qlik Community Tip: How to Get Answers to Your Post

Best,

Sunny

Re: set analysis to calculate a double weighted average

cocuzzan — Tue, 30 Oct 2018 11:21:07 GMT

I have only one dimension "reference"
a "total revenue" measure
an "internal hours" measure
a "distribution hours" measure

I have to calculate the weighted average revenue for the internal hours but in the total the weighted average must appear with respect to the hours of distrubizione.

in the example I show you the total weighted average for the internal hours appears instead another value must appear

in the lines, on the other hand, the value must be the one weighed only for internal hours

follows the instruction I am using for the weighted average for the internal hours only.
Now I should in turn take this formula and nest it to calculate the weighted average with respect to the hours of distribution.

I expect then that in the rows the value does not change while in the total I should have get € 41.80

I hope it was clear. it's difficult

the total revenue column is not used in the calculation

sum ({$< Nr_Anno_Competenza={$(vMaxAnno)},Nr_AnnoMese_Competenza={'<=$(vMaxMese)'}>}

REVENUE *HH_INT )

sum({$< Nr_Anno_Competenza={$(vMaxAnno)},Nr_AnnoMese_Competenza={'<=$(vMaxMese)'}>} HH_INT)

Re: set analysis to calculate a double weighted average

sunny_talwar — Tue, 30 Oct 2018 11:32:13 GMT

So, I understand how you are getting 42.57...

((122,293 * 44.71) + (152,501 * 40.86)) / (122,293 + 152,501)

(5,467,720 + 6,231,191)/274,794 = 11,698,911/274,794 = 42.57

But, how are you getting 41.80? I mean what is the calculation based on the screenshot that you have shared?

Re: set analysis to calculate a double weighted average

cocuzzan — Tue, 30 Oct 2018 11:41:46 GMT

perfect .. leave now 47.50 and concentrate on the values of the lines.
now consider the calculated values with the HHDistribuctions

((40.86 * 228877) + (44.71 * 73868)) / 302745 = (9351914.22 + 3302638.28) / 302745 = 12654031.35 / 302745 = 41.80

Re: set analysis to calculate a double weighted average

cocuzzan — Tue, 30 Oct 2018 11:43:37 GMT

in practice I have to apply the second weighing to previously weighed values

I was thinking of making a formula like that but it does not work

Re: set analysis to calculate a double weighted average

sunny_talwar — Tue, 30 Oct 2018 12:00:03 GMT

May be something like this

If(Dimensionality() = 0,

Sum({$< Nr_Anno_Competenza={$(vMaxAnno)},Nr_AnnoMese_Competenza={'<=$(vMaxMese)'}>}

HHDistribuctions * HH_INT)/

Sum({$< Nr_Anno_Competenza={$(vMaxAnno)},Nr_AnnoMese_Competenza={'<=$(vMaxMese)'}>} HHDistribuctions),

Sum({$< Nr_Anno_Competenza={$(vMaxAnno)},Nr_AnnoMese_Competenza={'<=$(vMaxMese)'}>}

Revenue * HH_INT)/

Sum({$< Nr_Anno_Competenza={$(vMaxAnno)},Nr_AnnoMese_Competenza={'<=$(vMaxMese)'}>} Revenue)

)

Re: set analysis to calculate a double weighted average

cocuzzan — Tue, 30 Oct 2018 13:07:46 GMT

thanks but it does not work, in practice the formula when the "Dimensionality () = 0" is wrong.

I would like them as I told you the double weighing formula suggested earlier.

Re: set analysis to calculate a double weighted average

sunny_talwar — Tue, 30 Oct 2018 13:09:53 GMT

cocuzzan‌I want to help, but I don't think I understand your problem well enough to help. I hope someone else is able to help.

All the best,

Sunny