Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

joeallen
New Contributor III

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

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

Capture.PNG

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? 

1 Solution

Accepted Solutions

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

How about this?

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

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

How about this?

Sum(Lines)/Aggr(NODISTINCT Sum(Coefficient), MonthField)
joeallen
New Contributor III

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

That works perfectly.