4 Replies Latest reply on Jan 10, 2019 4:16 AM by Eoghan Lyons

# Growth in N periods even though Date not in chart

Hi All,

Having a spot of bother with this one. I wish to draw up a scatter plot chart. One one axis I want to show the average price of an item, on the other I want to show the growth in a different metric (called "Value") over N periods (I wish this to be flexible - but if that can't be flexible I won't lose any sleep).

I attach a file with 1) the desired chart (almost) and 2) an example of the calculation I want to include in the scatter plot. The Scatter Plot chart will ideally have a different X axis, this is where I want to show the Growth in the "Value" measure over N periods. For the sake of ease let's use quarters or months if it's easier as a standard period length.

If this has to be using a fixed rather than dynamic N change that's fine.

Can this be done easily?

Many thanks,

Eoghan

• ###### 1. Re: Growth in N periods even though Date not in chart

Eoghan,

I'm not sure if I caught the gist, but maybe the below can give some ideas.

I started with your growth table, and I dragged the Value Percent Difference pill

over to the Measures area, which generated this calculated field:

(ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -1)) / ABS(LOOKUP(ZN(SUM([Value])), -1))

I created the N parameter, and replaced the -1s in the above:

(ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -[Select N Months])) / ABS(LOOKUP(ZN(SUM([Value])), -[Select N Months]))

This I placed on the Columns Shelf, AVG([Price]) on the Row's Shelf, and Month([Date])

on the Detail shelf. The Growth compute using is set to month.

Growth in N periods even though Date not in chart

1 of 1 people found this helpful
• ###### 2. Re: Growth in N periods even though Date not in chart

Hi Swaroop,

Many thanks for the suggestion. I've had a play around with it and I couldn't understand where my issue was, and then I realised.

Long story short of the below. Is it possible for me to isolate the value at a certain point in time?

Example:

(New - Old)/Old

- New is always the most recent date period.

- Old is a manually selected date.

What I'm looking for is a single point in time comparison to another single point in time.

For example: I want to see the comparison to now versus X months/quarters ago. The worksheet you presented did show this, but it also showed the prior months versus X months before that particular month. I only want to show the comparison versus the latest date in the database.

I have tried to arrange this myself but the problem is that let's say I'm looking at weekly data. If I want to see a week versus a year previously, I would need to include all the weeks in between those dates in order to get the calculation I need.

Eoghan

• ###### 3. Re: Growth in N periods even though Date not in chart

Eoghan,

Apologies for being off track.

Maybe the below will be closer to the goal.

I think this should make it simpler now.

The Most Recent Month Value will be an LOD that fixes it to every row:

{ FIXED :MAX(

IF [Date (Months)]={MAX([Date (Months)])}

THEN [Value]

END)}

Then you can filter down to the desired month and get the difference by

([Most Recent Month Value]-[Value])

/

[Value]

As the values change from month to month, the automatic axes change too,

so I made some reference bands to fix the graph to max and min differences and prices.