9 Replies Latest reply on Jul 23, 2018 5:42 AM by Mahfooj Khan

# Need help to calculate last 2 or more days back average profit on day, state and city

Hi,

I need to calculate last 2 days average profit on a tabular report where each row contain order_date, state ,city and sum(profit), last 2 days back Average profit.

Last 2 days back average profit:

In the report for each row  which have order_date, state, city and there are calculate field "last 2 days average profit". This calculated filed will search profit last two day profit with same city and state and will do average with current order day's profit.

Below screenshot show  on order day (12/30/2017) , state (New York city) and city (New York) have one profit available on 29th Dec 2017. So it show average 19. But other days have on profit available so it show only that day's profit.

I have user fixed, moving calculation but not getting the exact result.

Thanks and Regards,

Amiya Basak

• ###### 1. Re: Need help to calculate last 2 or more days back average profit on day, state and city

Hi,

Find my approach below,

Last 2 Days:

IF [Order Date]>=

{FIXED [City]: MAX(IF [Order Date]>={FIXED [City]:MIN([Order Date])}

AND [Order Date]<{FIXED [City]:MAX([Order Date])}

THEN [Order Date] END)}

THEN [Order Date]

END

Last 2 Days Avg:

{FIXED [City]:SUM(IF [Order Date]=[Last 2 Days] THEN [Profit] END)}/2

This is my observation,

Let us know if this help. Workbook v10.5 attached for your reference.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: Need help to calculate last 2 or more days back average profit on day, state and city

You should sort by city name , date and then moving average will work for you.

• ###### 3. Re: Need help to calculate last 2 or more days back average profit on day, state and city

Hi,

I'm glad what you did is useful ,but what confused me is :

such as the city Scottsdale,there are only one orderDate for it , and profit is 111.57,then last 2 days average should be 55.785 not 67.68;

and another city: like PineBluff,Rogers,Springdale,it's blank for last 2 days average ,different from Scottsdale,I don't know why.

I'm not sure if you can understand what I mean,but i’m looking forward for your reply!

thank you!

Oliver King

• ###### 4. Re: Need help to calculate last 2 or more days back average profit on day, state and city

Hi,

First you need to apply context filter on your year filter,

Then do some modification in Last 2 days calculation,

Last 2 days:

IF [Order Date]>=

{FIXED [City]: MAX(IF [Order Date]>={FIXED [City]:MIN([Order Date])}

AND [Order Date]<{FIXED [City]:MAX([Order Date])}

THEN [Order Date] END)}

THEN [Order Date]

ELSE {FIXED [City]:MAX([Order Date])}

END

This would be the output, let me know if this help.

workbook attached with revised calculation.

Mahfooj

• ###### 5. Re: Need help to calculate last 2 or more days back average profit on day, state and city

Hi MAHFOOJ,

Thanks for your reply. But look like solution not working for me. Here are my concern

Both the marked city do not have any value of 2 days back but still doing some calculation. I do not know with which value.

Second, My requirement was to get date in first column then state and the city, profit and 2 days back avg or sum profit.

it will show same profit if last 2 days back it do not find any value on that city and state

Hope you understand.

Thanks and Regards,

Amiya Basak

• ###### 6. Re: Need help to calculate last 2 or more days back average profit on day, state and city

Also

I used your formula and arrange the column as I want but here are something I got

See No 1  the New work city have value on 29th so the average is 19 is ok. But no 2 is also showing 19 whereas no value in 28th Dec.

See No 3 city do not have value 2 days back but the value still change.

Thanks and Regards,

Amiya Basak

• ###### 7. Re: Need help to calculate last 2 or more days back average profit on day, state and city

Hi,

find the revised calculation

IF [Order Date]>={FIXED [State],[City]:MAX([Order Date])-1}

AND [Order Date]<={FIXED [State],[City]: MAX([Order Date])}

THEN [Order Date]

END

Here the revised output

Let us know if this help.

Mahfooj

• ###### 8. Re: Need help to calculate last 2 or more days back average profit on day, state and city

Hi Mahfooj,

The revised out still not matching with my requirement. I am giving another screenshot if that can help

It work like

take date , city , state and go to previous date and find same state and city. if the record is there and it will add it with current row. It is applicable for each row. It will only check this three column. It could be possible there are more column in the tabular report and it will ignore them like you have used Fixed. The original data have hour also.

• ###### 9. Re: Need help to calculate last 2 or more days back average profit on day, state and city

I got your point. I'll get back to you once resolve it