Difference in value between 2 Quarters

system · August 29, 2018, 10:43pm

Hi All,
I have a report in which i have to show value difference between 2 Quarters.
The Report shows Mill Name, Metric Name and Value under Max(Quarter) , meaning the latest quarter.
An extra row shows a measure called DIFF, whose formula is:

=Value where Quarter=Min(Quarter)  MINUS  Value where Quarter=Max(Quarter)

SO basically we want to see the difference in value between 2 Quarters, unfortunately i am getting a '0" as answer, as it is taking Value from both Quarters.
Any clues, anyone??

americanmc (BOB member since 2009-12-31)

system · August 30, 2018, 8:23am

Your result is dependent on the other columns in your table.

Create separate variables for your max and min

Try pulling your variables in two different single cell tables

If you are getting the right results in each table, then you should use Calculation Contexts to achieve the correct results

sum(value) in (YEAR) where…

jemstar (BOB member since 2006-03-30)

system · August 31, 2018, 1:40pm

Thanks for the tips. I feel i am a little closer however using your suggestion of
=Value In (Year) doesnot work for me.

I am trying something like this:

=RelativeValue([Value],[Quarter field];-1)

it is getting closer but not final still.
Someone suggested using =Previous function, still not able to figure out correct syntax.

Trying to get difference in Value between 2 Quarters.

Mill | 2017-Qtr4 | 2018-Qtr1 | Diff.
Cedar | 55 | 60 | (5)

Need to show value in brackets, which is the difference of value from Qtr 1 and prvious Qtr (60 - 55 = 5).
Asking help for more insight please!

americanmc (BOB member since 2009-12-31)

system · August 31, 2018, 3:33pm

So it’s the previous value within a Crosstab?

mikeil (BOB member since 2015-02-18)

system · August 31, 2018, 3:38pm

Hi,
It’s the difference of value between 2 Qtrs, as you can see 2017-Qtr4 value is 55 , while 2018-Qtr1 value is 60, so if we minus those, get 5 as result.
I have to show this value in brackets…
i hope it is clear for you.

americanmc (BOB member since 2009-12-31)

system · August 31, 2018, 3:53pm


"("+ ([Quantity]-[prev]) + ")"

should work, where [prev] is simply defined as

previous([Quantity])

mikeil (BOB member since 2015-02-18)

system · August 31, 2018, 5:01pm

Thanks it worked.

americanmc (BOB member since 2009-12-31)

system · September 4, 2018, 9:02pm

can you create a sequence for your qtrs ?
Meaning
Yr Qtr sequence
2017 1 1
2017 2 2
2017 3 3
2017 4 4
2018 1 5
2018 2 6
2018 3 7
2018 4 8

pull the sequence in your report and you it to do the calculation.

meettaurus (BOB member since 2005-03-10)

system · September 4, 2018, 9:34pm

How do you suggest to create an extra column called “Sequence”?

americanmc (BOB member since 2009-12-31)

system · September 5, 2018, 1:56pm

Can you create that column Sequence in the Universe, using RowNum() function and partition by Year and Qtr only. Makes sense?

M H Mohammed (BOB member since 2010-06-16)