Calculating per date when one of the values doesn't have an entry for every date

[joeallen]

Hello, i have  a particular problem i'm having trouble finding a solution.

I have one table that has a value (Lines) for every working date per month.

Then I have another table that has another value (Coefficient) for only one date every month (first working date every month). But this value is valid for the entire month.

I need to make a calculation in a pivot with both of these values, for every date, that is simply sum(Lines)/sum(Coefficient).  However this doesn't work currently since the second value(coefficient) is only on one Date per month (but as said, this value is still valid for all dates that month).

Here is an example where I just took a couple of days from 2 months

So I need for example 2019-05-02 to be 2287/61.  2019-05-03 to be 2509/61, etc.

Then 2019-06-04 to be 2812/60. Since the coefficient for June is now 60 as seen on 2019-06-03.

Surely there must be some way of writing an expression that makes this right? Obviously sum(Lines)/sum(TOTAL Coefficient) works as long as you only have one month in selection, but if you have multiple months it doesn't work. Anybody can help? 

[sunny_talwar · Accepted solution]

How about this?

Sum(Lines)/Aggr(NODISTINCT Sum(Coefficient), MonthField)

[joeallen]

That works perfectly.