6 Replies Latest reply on Jun 2, 2016 3:07 PM by Rohit Garg

# Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3

Hi, I am looking to calculate difference of values calculated on two sheets (with same data-source) in the third sheet. I have 2 parameters - Date Range (Day/Week/Month/Year) and Measures (Units, DRYS/Unit, Revenue, \$ per DRY). Common calculated fields:

Calc_Date Display:

IF [Date Range] = "Day" THEN DATETRUNC('day',[Order Date])

ELSEIF [Date Range] = "Week" THEN DATETRUNC('week',[Order Date])

ELSEIF [Date Range] = "Month" THEN DATETRUNC('month',[Order Date])

ELSEIF [Date Range] = "Year" THEN DATETRUNC('year',[Order Date])

END

Calc_Measures to Display:

CASE [Measures]

WHEN 'Units' THEN SUM([Product Unit Qty])

WHEN 'DRYS/Unit' THEN sum([Calc_DRYS])/sum([Product Unit Qty])

WHEN 'Revenue' THEN SUM([GCR Amt])

WHEN '\$ per DRY' THEN SUM([GCR Amt])/SUM([Calc_DRYS])

END

Sheet 1 (Current Term)

• By current term, I mean - If I select 'Day' in Date Range parameter then the sheet should show me Today data. I am able to achieve this with the help a calculated field -

Calc_Current Term

CASE [Date Range]

WHEN 'Day' THEN IF DATETRUNC('day',TODAY()) = DATETRUNC('day',[Order Date]) THEN "Show" END

WHEN 'Week' THEN IF DATETRUNC('week',TODAY()) = DATETRUNC('week',[Order Date]) THEN "Show" END

WHEN 'Month' THEN IF DATETRUNC('month',TODAY()) = DATETRUNC('month',[Order Date]) THEN "Show" END

WHEN 'Year' THEN IF DATETRUNC('year',TODAY()) = DATETRUNC('year',[Order Date]) THEN "Show" END

END

This is how it looks like:

Sheet 2 (Previous Term)

• Similarly I have a sheet 2, where in I have only 1 difference i.e. Calc_PreviousTerm to show previous day data.

CASE [Date Range]

WHEN 'Day' THEN IF DATETRUNC('day',DATEADD('day',-1,TODAY())) = DATETRUNC('day',[Order Date]) THEN "Show" END

WHEN 'Week' THEN IF DATETRUNC('week',DATEADD('week',-1,TODAY())) = DATETRUNC('week',[Order Date]) THEN "Show" END

WHEN 'Month' THEN IF DATETRUNC('month',DATEADD('month',-1,TODAY())) = DATETRUNC('month',[Order Date]) THEN "Show" END

WHEN 'Year' THEN IF DATETRUNC('year',DATEADD('year',-1,TODAY())) = DATETRUNC('year',[Order Date]) THEN "Show" END

END

Problem: I want to show difference of values calculated on sheet 1 and sheet 2 for identical parameter values(Ex - For Day and Units combination, I would like to see difference of sheet 1 value and sheet 2 value) in sheet 3. I would really appreciate if someone could please assist me.

P.S. I have tried searching the forum, but couldn't find anything relevant. If it's discussed already, please share the link.Esther AllerJonathan DrummeyNicholas Hara

• ###### 1. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3

Hi Rohit,

Thank you for tagging me in your post, I appreciate that you trust my expertise. It is, however, seen as poor etiquette to tag multiple people within a post, unless there is a specific ask for their area of expertise. I'd ask that in the future you please limit your tagging to relevant and essential circumstances. Doing so ensures that you get a good response when tagged and that our expert community members are utilized for their specific skills.

Anyways, I took a quick look at the problem above and I would suggest the following approach:

1. Revamp your calculations to be less verbose

2. Use a period over period quick table calc instead of attempting to compare two sheets.

When using a parameter to select different date levels, I always inject the parameter into the DATETRUNC() calc instead. Your new calculation for your current and previous periods should look something like this:

DATETRUNC([Date level Parameter],DATEADD([Date level Parameter],-1,TODAY()))>=DATETRUNC([Date level Parameter],[Date])

This creates a boolean that you can use to show the values for both the previous period and the current

a. Add your Date field to the Columns shelf, Make it MDY.

b. Add the new Boolean calculation to your filters shelf and filter to TRUE

c. Add the Measure to Display calc to the Text shelf

d. Right-click on the Measure to Display> Quick Table Calculation > Difference

3. Hide the empty values

a. Right-click on the Measure to Display>Filter

b. Select Special> Non-null values

Hopefully this helps!

1 of 1 people found this helpful
• ###### 2. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3

Thank You  Nicholas Hara  for your detailed response and letting me know about best practice for tagging people. I will keep in mind.

Regarding the solution you suggested:

1) I created a calculated field, which looks like this:

DATETRUNC([Date Range Poc],DATEADD([Date Range Poc],-1,TODAY()))<=DATETRUNC([Date Range Poc],[Order Date])

2) Built view

c. Added measure to display text shelf

d. Added Quick Table Calculation > Difference

3) Hide empty values

This logic works perfectly when I have 'day' selected as parameter value. However, when I select 'week', 'month', 'year then, data (Difference of values) is displayed for last week, month, year respectively. Instead, I want to show week over week, month over month, year over year difference. Please refer:

Day

@

Week

Month

Looking forward for your response. Thank You !

• ###### 3. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3

Hi Rohit, that was my fault.

You need to create a custom date that is responsive to the parameter as well. (facepalm)

Instead of Order Date use a date field with the following calculation: DATETRUNC([Date Parameter],[Order Date])

Hopefully this helps!

• ###### 4. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3

Thank You Nicholas Hara. That worked

I have to ask you one more thing -

Scenario:

1) Month - May (M1/current month), April (M2), March (M3)

2) Week - Week starting: 23rd May (Week 1/current week), 16th May (Week 2), 9th May (Week 3)

3) Day - 24th May (Day 1, current day), 23rd May (Day 2), 22nd May (Day 3)

If I want to show difference of M2 Vs M3, Week 2 Vs. Week 3, Day 2 vs Day3. Then, what shall I do different?

I tried modifying the field you mentioned earlier as follows: DATETRUNC([Date Range Poc],DATEADD([Date Range Poc],-2,TODAY()))<=DATETRUNC([Date Range Poc],[Order Date])

I get following:

Goal: I want to show difference value only for 23rd of May(day), 16th May(week),  1st April(Month).

Thank You for your continued support.

• ###### 5. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3

Hi Rohit,

I'm glad I resolved your initial question. Be sure to mark the answer as correct so that other community members with similar questions can search for and find the answer.

As for your new question, I think in order to change the two periods that are being compared, you can use a string parameter ([Period]) to dictate the range.

AND

When you enter a number, it will now look at the 2 periods that you dictate.

• ###### 6. Re: Calculate difference of value 1(Sheet 1) and value 2(Sheet 2) in Sheet 3

Hello Nicholas Hara, Hope you are doing well. I am trying to implement an additional functionality i.e. Year over Year percentage difference in metrics (Calculated field - Calc_Measures to Display, as mentioned above). However, % difference is being calculated with respect to previous value(Ex - % diff of May(2016) and April(2016) value). Please refer:

Where, Calc_Time_Filter is defined as follows:

If [Date Range]="day" AND DATEDIFF('day',[Order Date],TODAY())<28 THEN "Show"

ELSEIF [Date Range]="week" AND DATEDIFF('week',[Order Date],TODAY())<15 THEN "Show"

ELSEIF [Date Range]="month" AND DATEDIFF('month',[Order Date],TODAY())<13 THEN "Show"

ELSE "Hide"

END

GOAL: I want to show % difference between YOY value. For Ex - % diff between May(2016) and May(2015). Please refer (This chart contains same data):

Waiting to hear from you.