1 Reply Latest reply on Jul 27, 2017 8:31 AM by Vincent C

Average number of days across dataset, with month as dimension

Hi all,

I'm struggling to do this calculation and hope you can help me with.

I've been able to solve this problem using custom SQL, I did the calculation with a select and join on the primary table, but maybe a calculated field would be possible.

As my data are confidentials, I'll try to explain my case with an example. I've made a small dataset with silly data.

My data are orders informations. Classic infos, where I have a discountid field for a discount code. I'm trying to get the average number of days a code has been used by customers each month. If a code has been used in January, but started to be used in December, then I have to count the number of days in December AND in January (same with a code started in January and ended in February, I would have to look in January AND in February, even if I want to do the calculation for January), sum across all discount code used in January, and then divide by the number of distinct codes of the current month. For a code to be considered used a day, it has to be used at least 3 times this day.

I have a view with month of order as dimension.

For example, to get this number for March 2017, using SQL:

SELECT SUM(D.NB) / COUNT(*)

FROM (

SELECT C.DISCOUNTID, COUNT(*) AS NB

FROM (

SELECT B.DISCOUNTID, DATE(B.ORDERDATE) AS ODATE, COUNT(*) AS NBC

FROM SAMPLE_TABLE B

WHERE B.DISCOUNTID IN (

SELECT DISTINCT A.DISCOUNTID

FROM SAMPLE_TABLE A

WHERE DATE(A.ORDERDATE) >= '2017-01-01' AND MONTH(A.ORDERDATE) = 3

)

GROUP BY B.DISCOUNTID, DATE(B.ORDERDATE)

HAVING NBC >= 3 ) C

GROUP BY C.DISCOUNTID ) D;

Hope you can help me, thanks in advance.

Regards,

Vincent

• 1. Re: Average number of days across dataset, with month as dimension

No one can help a bit?

Vincent