set analysis to calculate a double weighted average

[cocuzzan]

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

[sunny_talwar]

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

[cocuzzan]

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

[sunny_talwar]

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

[cocuzzan]

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)

[sunny_talwar]

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?

[cocuzzan]

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

[cocuzzan]

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

[sunny_talwar]

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)

)

[cocuzzan]

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.