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(*)
SELECT C.DISCOUNTID, COUNT(*) AS NB
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.
No one can help a bit?