8 Replies Latest reply on Jul 24, 2015 9:41 AM by Ryan Ray

# calculate this?

Hello All,

There are two metrics: metricA and metricB. Please see the attached file

User can choose two dates – start date, end date

I want to create a calculated filed that does this:

Let’s say if my start date is 2/1 and end date is 2/6, I would need to do the following

Sum of MetricB (from 2/1 through 2/5) – sum of metricA (from 2/1 through 2/5)

So the formula will show:

(1+3+7) – (2+4+5) = 0

So, I sum up both the metrics till the day < than the end date given by user

• ###### 1. Re: calculate this?

see attached....

• ###### 2. Re: calculate this?

Just make a calculation

SUM(A) - SUM(B)

and drag the date dimension on the Filters shelf and select Ranges of Date.

Next right-click the green pill on the Filters shelf and select Show Quick Filter.

This way the user can select the range of date and the calculation will adjust accordingly.

• ###### 3. Re: calculate this?

Thank you Michel,

But one issue

When I select the range of date from 2/1 to 2/6, it takes into accounts the metrics from 2/1 to 2/6/.

Because of this, the result is 1. Rather it should be 0 if it takes into account 2/1 through 2/5

So the calculation needs to accounts for only days  2/1 through 2/5 if 2/6 is selected as the date range end date

• ###### 4. Re: calculate this?

so it needs to take into account EndDate -1 as the end date for the range really

• ###### 5. Re: calculate this?

You can try this...see attached

gota go....

• ###### 6. Re: calculate this?

Hello Michel,

Yes this is what I needed. You are my hero. Thank you a  million. Can you please explain the formula? Thank you for your kindness

• ###### 7. Re: calculate this?

like

IF DATEDIFF('day', [Date1], { FIXED :Max([Date1]) }) > 1 then [Metric A] END

what does than mean?

• ###### 8. Re: calculate this?

Thank you Michel, I understood your logic,thanks gain, this is a lifesaver for me:)