Scenario
8:
Problem:
How
to calculate Year
over Year
Growth
when
quarters are like 15Q1, 15Q2......
For
problem description see below image
Solution:
Note:
I don’t
have Quarter like 15Q1, 15Q2 ….. in sample super store data source.
So I am creating calculation field as “Year-Quarter” using below
formula.
right(str(year([Order
Date])),2)
+ 'Q' + str(datepart('quarter',[Order
Date]))
Step
1:
User want to see current quarter and last 4 previous quarter. So darg
“Year-Quarter”
column to filter
shelf
and select last 5 quarters in the filter list. I.e 12Q4, 13Q1, 13Q2,
13Q3 and 13Q4.
Step
2:
Now create a calculate field “Year
over Year Growth”
using below formula.
(sum(Sales)
– lookup(Sum(Sales),-4))/lookup(Sum(Sales),-4)
See
explanation below:
Step
3:
Drag
Year-Quarter
Column to Row
Shelf
and Region
Column to Column
Shelf.
Step
4:
Drag “Year
over Year Growth”
field to Text
Shelf
see
below
In
above image you are seeing blanks for each each Quarter. Because by
default Year
over Year Growth
table calculation computing across
table.
We need to compute this along Table
Down.
See below
Then
you will see like below
Step
5:
Right click on Year
over Year Growth
field which is there in text
shelf
and change number format to percentage.
Note:
User wants to see only last row. Because Other rows showing blank.
Step
6:
Create a Calculate filed “Show
Last Row”
using below formula.
if
last()
= 0 then "Show" else "Hide" END
Note:
In above formula, some people may directly use “last()
=
0”
only instead of entire formula which I used. I like this kind of
usage
in formula.
In
future
maintenance people can understand easily, that
we are doing some show and hide functionality.
Step
7:
Drag “Show
Last Row”
field to filter
shelf
and select “Show”.
After applying filter you will see like below
This
is not desired output. “Show
Last Row”
is computing along table
across.
Right
click
on “Show
Last Row”
which is there in filter shelf, go to compute using and select Table
Down
then it will pop up once again for filter selection. Select “Show”.
Click OK
button.
Here
you will see final output:
