date over year on same chart

[#1]

Hi,

I would like to plot ACT and LY ACT over date on the same chart.

Output chart should be something like:

Date	ACT	LY
26-7-2015	150	170
27-7-2015	164	213
28-7-2015	272	277
29-7-2015	308	380
30-7-2015	382	418
31-7-2015	487	437
1-8-2015	515	513
2-8-2015	613	649
3-8-2015	735	784
4-8-2015	833	929
5-8-2015	838	936
6-8-2015	960	1069

To achieve that end, I've created periods table

periodId	periodNm	LY_periodId
10	26-7-2014	null
11	27-7-2014	null
12	28-7-2014	null
13	29-7-2014	null
14	30-7-2014	null
15	31-7-2014	null
16	1-8-2014	null
17	2-8-2014	null
18	3-8-2014	null
19	4-8-2014	null
20	5-8-2014	null
21	6-8-2014	null
22	26-7-2015	10
23	27-7-2015	11
24	28-7-2015	12
25	29-7-2015	13
26	30-7-2015	14
27	31-7-2015	15
28	1-8-2015	16
29	2-8-2015	17
30	3-8-2015	18
31	4-8-2015	19
32	5-8-2015	20
32	6-8-2015	21

(NOTE: period can also be a month, or quarter)

...and source table

PeriodId	margin
10	170
11	213
12	277
13	380
14	418
15	437
16	513
17	649
18	784
19	929
20	936
21	1069
22	150
23	164
24	272
25	308
26	382
27	487
28	515
29	613
30	735
31	833
32	838
33	960

Business requirements:

reference period = selected period

chart scope:

min_periodId = [reference period] - $(periodsBack)

max_periodId  = [reference period] + $(periodsForward)

Dimension: periodNm

Expressions:

• margin CY
• margin LY

Question: how can I plot two parallel periods with 2 different periodId?

[swuehl · Accepted solution]

Maybe like attached?

[sunny_talwar]

If you want the front end solution, you can try this. Right now it isn't going to work when you make a selection, but when you have continuous dates, the solution should work flawlessly. PFA

Expression 1: Sum({<periodNm = {"$(='>=' & Date(Max(periodNm)-12) & '<=' & Date(Max(periodNm)))"}>}margin)

Expression 2: Above(Sum({<periodNm = {"$(='>=' & Date(AddYears(Max(periodNm)-12, -1)) & '<=' & Date(AddYears(Max(periodNm), -1)))"}>} margin),12)

You should be able to use a variable to determine how many days you want to go back based on user input in a input box and use the variable to replace 12 (highlighted in red)

Best,

Sunny

[swuehl]

Sunny,

isn't that also a script based solution, just a different one (using the OPs script based solution)

My intention was to show that if we change the script a little, it makes the front end much easier.

[sunny_talwar]

Hahahaha sure Stefan, Its a front end script based solution

I, by no means, wanted to suggest that my solution is better than yours. In fact, I would love if you or somebody else can point out which one is better solution (always trying to learn a more efficient way of doing stuff), which in this case would definitely be yours because you simplify everything in the script. But I think listing out all possibilities is also a good idea, giving the Questioner/Poster a chance to decide which options suits him the best for his situation. I am sure you will agree.

Best,

Sunny

[#6]

swuehl,

I like this approach. It seems to be clean and elegant. Can I also split it into two expressions?

• CY - Sum({<Type = {'CY'}>}Value)
• LY - Sum({<Type = {'LY'}>}Value)

The solutions only works if 'AsOfDate' is used as dimension in the chart. In my model its important to use date as dimension. Is it possible to revert the AsOf table?

[sunny_talwar]

Correct me if I am wrong Stefan, but I think this model can only work if you create a new field name, you can call it whatever you want, but this is the field you will need to use to make the chart to work.

HTH

Best,

Sunny

[swuehl]

Dror,

1) Sure, you can exactely use these two expressions instead of the dimension Type in the chart

2) Not sure what you mean with reverting the AsOfTable.

As Sunny, said, you can rename fields, but there will be essentially one field that shows the book-keeping date (where the value was booked) and another field that acts as a translation / link to another date (same date as bookkeeping for CY, one year ahead for LY).

If you make selections only on the book keeping date, you can use some set analysis to apply these selections to the chart.

If it's just the wording AsOfDate, you can rename the field or label in the chart.

Anyway it will be hard to fulfill every possible requirement, for example, in Sunny's solution, you will see some issues if you select a PeriodNm.

[#9]

swuehl and Sunny,

sorry for my late reaction. I've implemented the AsOf approach and it works like  a charm

THANKS ALOT

Dror