10 Replies Latest reply on Apr 25, 2018 2:22 AM by Maikel Korff

# Help with Calculation

Hello,

We get hour to date data each day and compare to same day last week. In the example below, today's data is up to 11 AM. but last week's data is available for entire day.

Is there any way I can filter out the future hours for the day so that I can get total volume until 11AM for today and -7 days ?

So, I expect the application volume to be total of 6+18+29+43+28 = 124

and the -7 volume to be 8+22+24+45+44 =  143

Is there a way to do Hour of Application Date = Hour of Application Date - 7 Days so that I can exclude the data in RED?

Message was edited by: Venkat Sivaneswaran

Message was edited by: Venkat Sivaneswaran

Message was edited by: Venkat Sivaneswaran

• ###### 2. Re: Help with Calculation

Thanks Deepak. Just added a sample workbook.

• ###### 3. Re: Help with Calculation

Hi Venkat, Would you please extract data and reattach. I see this

• ###### 4. Re: Help with Calculation

my bad Deepak. Uploaded with sample extract. Thanks so much for the help.

• ###### 5. Re: Help with Calculation

Hi Venkat,

You need to use the Following calc to get what you need

Thanks

Deepak

If it Helps, pl mark it Helpful and CORRECT to close Thread

• ###### 6. Re: Help with Calculation

Thanks Deepak. I tinkered with LOD calc as well. Appreciate the response.

This calc is hardcoding the 11 AM. The data keep changing with next refresh and we need to compare to the latest data available for the day. So, it has to be dynamic.

(FIXED DATETRUC('hour', [Application Date]):SUM(IF INT([Application Date Hours])>11 THEN 0 ELSE [Application Volume -7 Days] END) )

So, as the day moves, we need to compare to the latest hour and compare to the last week for the same hour. Any chance how we accomplish this ? Much appreciated.

• ###### 7. Re: Help with Calculation

didn't I attach something?

• ###### 8. Re: Help with Calculation

attachment

• ###### 9. Re: Help with Calculation

Hi Venkat,

Here is the Dynamic Calculation, it will take care of your last day and last hour. We are getting 143 same result as was with Static calc.

See attached.

Thanks

Deepak