Skip to Content
0
Jan 17, 2023 at 04:13 PM

Product of Sums

231 Views Last edit Jan 19, 2023 at 08:13 AM 2 rev

Hello Math Experts,

i need to calculate the Product-Sum of 2 Numbers A and B (of different Type 1 and 2) and write this into another field C (To get the Average Value of a Quarter Q1). It is a Sub-Rule of a Join, so i want to use Window Functions or smth. simillar. ->SUM(A*B)/SUM(B) for all Quarters. So the simple calculation would be (3*5+7*4+2*8)/(5+4+8)=3,47 for Q1.

image.png

I know how I can calculate the Denominator :

CASE WHEN Month='Q1' and Type='1' then SUM(B) OVER (ORDER BY Identifier ROWS BETWEEN 3 PRECEDING and 1 PRECEDING) else 0 end.

This would get me (5+4+8=17).

But how is it possible to calculate Product-Sums over different Types 1 and 2. I know how to calculate a single value in a gap for example:

SUM(CASE WHEN (Month='JAN' OR MONTH='FEB' OR Month='MRZ') THEN A ELSE 0 END) OVER()

This would get (3+7+2=12).

But im not exactly clear how to calculate the Sum-Product of numbers A and B when they are of different Types 1 and 2. It would be possible to calculate sum(case when...)*sum(case when...) for each Month and then sum this up but this doesnt seem too bright. Im looking for a more fancy solution for this ''simple'' Problem.

Update (if anyone cares): you can do it with the following Logic:

image.png

ZEV_QUARTAL_SUMME was calculated like this, in a lower-level rule:

image.png

Regards, Philipp

Attachments

image.png (28.5 kB)
image.png (100.3 kB)
image.png (18.5 kB)