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

    Average number of days across dataset, with month as dimension

    Vincent C

      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