9 Replies Latest reply on Aug 23, 2018 7:34 PM by tian.yang.4

how to get 4-week average?

Hi team,

I want to identify today, yesterday, last week and four-week average.

I now use following formula to describe today, yesterday and last week, so I can get values(sales, profits) accordingly.

But I have no idea how to describe four-week average.

supposing today is Aug 21, I want value of ( Aug 21 + Aug 14 +Aug 7 +July 31)/4

IF DATEDIFF('day',DATETRUNC('day’,[DATE]),

{MAX(DATETRUNC('day’,[DATE]))}

)=0 THEN ‘TODAY’

ELSEIF

DATEDIFF('day',DATETRUNC('day’,[DATE]),

{MAX(DATETRUNC('day’,[DATE]))}

)=1 THEN ‘YEASTERDAY’

ELSEIF DATEDIFF('day',DATETRUNC('day’,[DATE]),

{MAX(DATETRUNC('day’,[DATE]))}

)=7 THEN ‘LAST WEEK’

END

YT

• 1. Re: how to get 4-week average?

Hi Tian,

• 2. Re: how to get 4-week average?

Hi, Tian Yang

Please find attached a sample of calculation fields as well as below screenshot

Hope this helps

ZZ

• 3. Re: how to get 4-week average?

Hi Zhouyi,

Thank you for answering, but this is not what i want.

In your screenshot, how you identify today? maybe you use if....then sum(value) and what's your calculation1:true?

this way can work for a little measures .. but i have plenty of them.

What I want is to first identify the date, so i can directly drag different measures into viz

(like profit, sales, freight, marketing fees etc.  I can't create calculated field for each of them.)

and the difficulty is each 'comparison date' only has one 'Day of date' and the measure value of '4 week'  also needs to divide by 4

maybe this way can't succeed ... any suggestions？

• 4. Re: how to get 4-week average?

Hi, Tian

I can get today/yesterday/last week/week before last week as shown below. Not very clear about your 4 week, it is overlap with these four dates, you can't  use one calculation for 4 week's avg unless you can union your data to itself.

ZZ

• 5. Re: how to get 4-week average?

Hi Zhouyi

However this is not what i want.

Please let me know if any alternative is available.

YT

• 6. Re: how to get 4-week average?

Hi, Tian

I am confused. Can you explain it a bit more?

ZZ

• 7. Re: how to get 4-week average?

hi zhouyi,

In 'comparison date' I have today, yesterday, last week, week before last week and 4 week.

4 week is different from the others because it has 4 dates (Aug 12, Aug 5, July 29 and July 22).

Then I need to add measures like profit, sales and it works well except '4 week'.

As you can see in the 'Day of date', there is only one date (July 22) assigned to '4 week' so does the profit and sales value.

I want the average value of profit/sales for 4 week : (measure value of Aug 12+ Aug 5+ july 29+ July 22) / 4

Hope it is clear for you.

YT

• 8. Re: how to get 4-week average?

Hi, Tian

In this case, you need union your data to itself to create a copy for 4 week use only.

If you could share a sample workbook, it will be easier to show how it works

ZZ

• 9. Re: how to get 4-week average?

Hi zhouyi

Here is the mockup.