how to create variance column for two dates

DB_Home

I have two date columns like date1, date2 and one measure(revenue) column. Now how can we create variance column as shown in the below table.

I can able to create this variance column in Pivot table. But I want to create same column in normal table.

eg:

   

Revenue		Variance
01/01/2017	02/02/2017
6000	9000	3000
2000	3000	1000

     

Thanks in advance,

Madhav.

[Deleted User]

3353428 wrote:I have two date columns like date1, date2 and one measure(revenue) column. Now how can we create variance column as shown in the below table.I can able to create this variance column in Pivot table. But I want to create same column in normal table.eg: Revenue Variance 01/01/201702/02/2017600090003000200030001000

Thanks in advance,

Madhav.

First of all you do not have "two date columns" - this isn't Excel. What you have is three measures - two of them filtered by a dimensional attribute "date" on two values "01/01/2017" and "02/02/2017".

Secondly - what's that magical "thingey" that splits your first row from the second? What splits 6000 from 2000 for the 1st of Jan 2017?

You have to take a step back and think about what you actually want to achieve. About your need behind all this. Analytical tools aren't Excel sheets doing cell calculation.

DB_Home

Hi

Above analysis is from pivot view prospective in OBIEE(I have one date column in table and taken comparison date column in filter and created new calculated column(variance) in pivot view on date column like ($1-$2). I have to create same variance column(revenue difference between two date) in normal table.This is my Requirement.

For this i'm trying to create one dummy column and inside column written below logic.

FILTER(revenue USING date1) - FILTER(revenue USING Date2)

But this is not working.

[Deleted User]

OK since you don't want to understand here's a context-less direct formula answer to your question:

SUM(FILTER("Revenue Metrics"."Revenue" USING ("Time"."Date" = timestamp '2013-12-02 00:00:00'))) -  (FILTER("Revenue Metrics"."Revenue" USING ("Time"."Date" = timestamp '2013-12-01 00:00:00')))