3 Replies Latest reply: Sep 27, 2015 12:00 PM by Edwin Witvoet

Find Prev Quarter (Pick in Time)

I have a data table with Metrics and Value per Network.

Each Quarter is labeled with a QuarterID, inspired by the Pick In Time post here (The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync)

Somehow I don't get it to work, meaning : per row displaying a specific Quarters value and Previous Quarter's value

My current expressions are

• Prev Q  : Max(QuarterID) - 1
• Max Q : Max(ds_value)
• Max 15-Q3 : max({<YY_Q={'15-Q3'}>} ds_value)
• Max 15-Q2 : max({<YY_Q={'15-Q2'}>} ds_value)
• Max (Prev Q) : max({\$<QuarterID = {\$(=Max(QuarterID) - 1)}>} ds_value)
• Max (Prev Q)(1) : Max(aggr(max({\$<QuarterID = {\$(=Max(QuarterID) - 1)} >} ds_value), ds_value))

What are the appropriate expressions for Max 15-Q3, Max 15-Q2, Max (Prev Q) to get values in all the Cells?

• Re: Find Prev Quarter (Pick in Time)

The set of a set analysis expression is calculated at the chart level, not the row level. So max(QuarterID)-1 will be 8058 for each row in the chart. So any expression using {\$<QuarterID = {\$(=Max(QuarterID))}>} - will only return a value for the row where QuarterID is 8058.

If you don't need the quarter as a chart dimension then simply remove QuarterID and YY_Q from the chart.

If you need the quarter as a chart dimension then there are several solutions. The easiest in my opinion is an AsOf table. See this document for more information and examples: Calculating rolling n-period totals, averages or other aggregations. In your case you'll need to link each quarter only with its previous quarter.

• Re: Find Prev Quarter (Pick in Time)

For the two static max values you can use TOTAL qualifier:

• Max 15-Q3 : max(TOTAL {<YY_Q={'15-Q3'}>} ds_value)
• Max 15-Q2 : max(TOTAL {<YY_Q={'15-Q2'}>} ds_value)

To get max ds_value from the previous quarter, you can use chart inter record functions:

=Above( Max(ds_value) )