3 Replies Latest reply on Apr 4, 2016 6:29 AM by Alex Welch

# Trouble with Month over Month Metrics

Hi All,

I am running into an issue where I need to compare the value of a variable  (account value in this example) to the value of the same variable just as of the last day of the previous month.  Below are my three (3) equations. Separately they work. [Selected Date] is a parameter and returns the correct value no matter what date I select. The [value last month] is also returning correctly. It is when I use equation #3 that it returns blank. I have no idea way and can't seem to get it to work.

1) -------------------------------

// Value Last Month

if [date_value] = [Last day of previous month] then

[account_value]

end

2) -------------------------------

// Value Today

if [date_value]= [Selected Date] then [account_value] end

3) -------------------------------

[Value Today] - [Value Last Month]

• ###### 1. Re: Trouble with Month over Month Metrics

You are using row level calculation. [date_value] cannot be equal to [Selected Date] and [Last day of previous month] at the same time - if the latter two are different dates -  just as #2016-04-03# may be equal to today but is not  equal to #2016-03-03#. The blank you are getting is a result of NULL returned when your condition is not met as you did not use 'ELSE' expression.

Please search this forums for possible answers on how to calculate this (this kind of question comes up every day here) or attach (using advanced editor) your packaged workbook (.twbx).

edit: not sure what you are doing but your formula may return what you expect if you use aggregates like this:

```SUM([Value Today]) - SUM([Value Last Month])
```
1 of 1 people found this helpful
• ###### 2. Re: Trouble with Month over Month Metrics

Create three Field and One Parameter.

Parameter Name: Date Selector (Date Type)

Calculated Field 1: Value Today// If User Select this as "1st April 2014" then this will return sales value for that day.

if [Order Date] = [Date Selector] then [Sales] END

Calculated Field 2: Value Last Month // This will return sales value for 31st March 2014

if [Order Date] = DATETRUNC('month',DATEADD('month',0,[Date Selector]))-1 then [Sales] END

Calculated Field 3: Differece Value

sum([Value Today]) -sum([Value Last Month])

Then arrange the Workbook in the above mentioned way.

• ###### 3. Re: Trouble with Month over Month Metrics

Ended up using MAX instead of SUM. But the next level of aggregation is what fixed it