1 Reply Latest reply on Dec 7, 2018 11:34 PM by Norbert Maijoor

Avg 3 Month Daily Sales

Need to create a calculation that gives me the average daily sales for the last 3 months (not dynamic to each month but always fixed to the most current rolling 3 months). I have a column with the work days for each month.

This is what 'ive got so far, though I feel i may have gone down a rabbit hole

 Month Sales Work Days 1/1/2018 25665 22 1/1/2018 23916 22 1/1/2018 19758 22 1/1/2018 25971 22 2/1/2018 25783 21 2/1/2018 23557 21 2/1/2018 25246 21 2/1/2018 24439 21 3/1/2018 25795 19 3/1/2018 19663 19 3/1/2018 23687 19 3/1/2018 20067 19 4/1/2018 22430 21 4/1/2018 20360 21 4/1/2018 22757 21 4/1/2018 22927 21 5/1/2018 25820 19 5/1/2018 22041 19 5/1/2018 21256 19 5/1/2018 25317 19 6/1/2018 23637 23 6/1/2018 24063 23 6/1/2018 22795 23 6/1/2018 23815 23 7/1/2018 20489 19 7/1/2018 25364 19 7/1/2018 19778 19 7/1/2018 25820 19 8/1/2018 22541 23 8/1/2018 25874 23 8/1/2018 21920 23 8/1/2018 19139 23 9/1/2018 21548 19 9/1/2018 20257 19 9/1/2018 25503 19 9/1/2018 22122 19 10/1/2018 20303 23 10/1/2018 19305 23 10/1/2018 22465 23 10/1/2018 22491 23 11/1/2018 23970 22 11/1/2018 19909 22 11/1/2018 21609 22 11/1/2018 21386 22 12/1/2018 25781 22 12/1/2018 24259 22 12/1/2018 21103 22 12/1/2018 24862 22

//sum last 3 month sales

SUM(

if [REPORT_DATE]  <= {fixed : max([REPORT_DATE]) }

and  [REPORT_DATE]  >=

date(

{fixed : max([REPORT_DATE]) }

)

)

then [Quantity] else 0 end

)

//this part seems to work

// now divide by last 3 month work days

/

SUM(

if [REPORT_DATE]  <= {fixed : max([REPORT_DATE]) }

and

[REPORT_DATE] >=

date(

{fixed : max([REPORT_DATE]) }

)

)

then

//here i need to get distinct count of work days per month

{ FIXED [REPORT_DATE] : MIN([Work_Days])}

else 0 end

)

In this scenario, what I would expect from the calc is the following :

Dec + Nov + October Sales = 267443

/

Dec + Nov + October Work Days = 22 + 22 + 23 = 67

= 3992

Looking forward to figuring this one out!

• 1. Re: Avg 3 Month Daily Sales

Hi Andres,

Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

1. M1. Workdays: sum([Work Days])/count([Month])

2. M2. Window Sum Sales: window_sum(sum([Sales]))

3. M3. Window_sum Workdays: window_sum([M1. Workdays])

4. M4. Average Last 3 months: [M2. Window Sum Sales]/[M3. Window_sum Workdays]

5. D1. Display:

DATEDIFF('month',[Month],today())<=2

and DATEDIFF('month',[Month],today())>=0

6. Drag the required objects to the indicated locations and filter D1. Display on TRUE

Regards,

Norbert