Calculation with Month's Last Day

[#1]

Hi, first post here, so forgive the newbie question:)

I'm using QVPE to evaluate it's possible uses for my company, I'm trying to reproduce the functionalities of a quite complex xls with qlikview in order to produce something more usable and appealing for one of our customers.

Unfourtunately, I'm stuck with a "stupid problem" atm

I have a serie of rows, each with 3 different dates (Recived, Closed, Limit)

I want to show some statistics based on those dates and grouped by the different months

I've loaded a calendar from an excel table with the script:

LOAD DATA,

month(DATA) as MONTH,

MonthEnd(Date([DATA])) as ENDMONTH

FROM

Calendar.xls

In qlikview, I've a pivot table with MONTH as dimension, and then the various expression, like:

MONTH-- stat-1--stat2--

jan

feb

mar

One expression, which I know works fine is:

TEXTCOUNT (DISTINCT IF (MONTH(Closed) > MONTH AND MONTH(Received) <= MONTH,ID)) + TEXTCOUNT (DISTINCT IF (ISNULL(Closed) <> 0 AND MONTH(Received) <= MONTH,ID))

which returns the number of "non closed" for each of the months shown

Now, I want to show the average "age" (in days) for the "non closed" for each months

I've tried many ways but none worked and I'm a little lost now as I can't see the mistake:

avg( distinct IF ((MONTH(Closed)>MONTH or ISNULL(Closed) <>0 ) AND MONTH(Received)<=MONTH, DAY(ENDMONTH)-DAY(Received)))

I know that what isn't working is DAY(ENDMONTH)-DAY(Received), but I really can't see why...

I someone can point me in the right direction it would be really appreciated 🙂

many thanks in advance

[#2]

Day(Month) gives you the day number in the month, so 22, or 31, or ...

day( '1971-10-30' ) returns 30.

When you subtract the days, you don't automatically receive the right number of days, since there could be a month transition or a period of several months.

What you need it to subtract the day numbers from eachother is the actual number that represents the date. Use Num(Date) to get that number.

Num(Day(ENDMONTH)) - Num(Day(Received))

[#3]

I tried but it didn't work

I think there should also be something wrong about the logic because I'm receiving really weird numbers as output

BTW, in the end I managed to have the correct result but really I can't tell why the formula is working.

I used this to take account of substraction when the dates are from different months:

MONTHEND(Received,MONTH-MONTH(Received)) - Received

What is strange is that:

1- I used a no distinct AVG formula

2-I noticed that the output of the nodistinct formula was adding 1 to the correct result, so I used :

(AVG( nodistinct expression) -1).

Honestly, I haven't understood why it is working, there's something I'm missing about logic here

Guess I'll have to make more tests to see if it works even with a different dataset

[#4]

In the end it is the result that counts

But it would be nice to know where this behaviour is coming from.