Product of Sums

[philippsathasivam]

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.

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:

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

Regards, Philipp

[jovanar · Accepted solution]

Hello Philipp,

Thank you for this very interesting question.

We have two solutions for you. In both of them we included additional field called Quarter (QUA) where we defined quarters with this formula (MON is Month):

CASE WHEN MON IN ('JAN', 'FEB', 'MRZ') THEN 'Q1'

WHEN MON IN ('APR', 'MAI', 'JUN') THEN 'Q2'

WHEN MON IN ('JUL', 'AUG', 'SEP') THEN 'Q3'

ELSE 'Q4'

END

Also, the Input Table looks like in your given example:

1) For the first solution you can use either Join or Calculation function.

• In the first rule we defined field D where we calculated Product of A and B for same month but different Types, like this:

  

• In the second rule we defined fields E and F which are used to calculate numerator and denominator:
• And, as you can see in picture above, in the final Rule 3 we are just dividing them to get desired value C.

2) In the second solution:

• We are first joining the Input Table with itself (Join Predicate is Month), in a way that in R0001 Type "1" is selected (and field A is set as "Not used") and in R0002 Type "2" is selected (and field B is set as "Not used"):

  

• Once quarters are defined like mentioned above, we just need to add one final Rule with the formula highlighted below to get desired value C:

Best Regards,

Jovana Radmilovic

[philippsathasivam · Accepted solution]

Hi Jovana, thanks for the Answer, very cool !