Welcome Guest! To enable all features please Login or Register.
Go to last post Go to first unread
#1 Posted : Wednesday, October 11, 2017 7:29:53 AM(UTC)
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 105

I'm trying to make a crosstab for sales amount pr shipment period, specification like this:

- dynamic period showing from this month to month +12
- compare each month to the same month previous year, year -2 and year +1. (this month, same month previous year, same month year -2 and same month year +1)
- keep everything dynamic

If it was generated today it would look something like this:
oct 2017 compared to oct 2016, oct 2015 and oct 2018.
nov 2017 compared to nov 2016, nov 2015 and nov 2018.
dec 2017 compared to dec 2016, dec 2015 and dec 2018.
jan 2018 compared to jan 2017, jan 2016 and jan 2019.
feb 2018 compared to feb 2017, feb 2016 and feb 2019.
.. and so on until sept 2018.

Is it possible?

Preferable presented in a bar chart with month by month starting with this month, next month, month +3...month +11, this would make 4 bars for each month.
Elin Petersen  
#2 Posted : Thursday, October 12, 2017 9:33:52 AM(UTC)
Elin Petersen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 6/26/2012(UTC)
Posts: 16

I just played around and I think you can solve it with Comparisons.
First you need a second date Hierarchy. You need one without the year, so just Quarter - Month - Day or something similar.

In this new hierarchy you can build a new Comparison where you build one element for each month.
In your other axis you set your "normal" date hierarchy and filter dynamically on "between this year -2 and this year +2".

Then you add an advanced calculation where you calculate 4 new columns which I called year -2, Year -1 This Year and Year +1. For each Column you check with an if-clause if there are values in the last column (at the Moment 2019). If there are values you take the column more left, e.g. For Year -2 I have this:
if sum(d-1, 0, m1) <> 0 then sum(d-4, 0, m1) else sum(d-5, 0, m1)

Then you just have to make the not-calculated-values invisible.

This works only if you plan exactly one year forward. If not, I hope this inspires you a bit. ;-)
Users browsing this topic
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.