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

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

This needs workbook to help you faster

• ###### 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

If these Helps,  PLease Mark them Helpful and CORRECT to close Thread.

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

Dear Venkat,

If you want to filter them out, you'll probably need a extra reference on Application Volume like 'processed = true/false'. Then you can compare both if true/true or true/false and exclude them as you wish. Otherwise you can't compare them correctly.