New post
Question
· Nov 9, 2016

Newbie Question for creating a this period/same period last year comparison

#Analyzer #MDX #InterSystems IRIS BI (DeepSee)

Hello. I'm just getting started in DeepSee, and am also a newbie in MDX.

I am looking to create a pivot table that will be used to drive a bar chart. The chart will have product classes across the bottom, and two bars per class: a calculated measure to derive Net Sales for the specified period last year, and a straight aggregation of a standard measure for the same period for this year. Ideally, this period can be chosen by the user via filters on the dashboard, and it could be week, month, or quarter. So, obviously, it's the calculated field (and perhaps the entire MDX query) I need help with.

My current query, which is just returning the product classes and the net sales (CLineExt), looks like this:

SELECT [Measures].[CLineExt] ON 0,NON EMPTY [WebCat1Desc].[H1].[WebCat1Desc].Members ON 1 FROM [INVOICEDETAIL]

The above cube, InvoiceDetail, also has an InvDate dimension, as well as 4 other dimensions derived from it: InvYear, InvQuarter, InvMonth, InvWeek

I *think* I need to use ParallelPeriod on any of these InvDate periods, but, as I say, MDX is still hard for me!

 

The result would be something like this:

                                        July 2015    July 2016

Candy                                867.00            589.00

Juice                                3361.00         2663.00

Candy                                393.00            293.00

 

The resulting chart have two sets of bars for each category, the first for the period for last year, the 2nd, for this year. Easy enough to do if the data can be gotten into the table above, but please note that I don't want a hardcoded formula (something like NOW.WEEK-52 for week, for example), rather, I am hoping to find something dynamic, that looks at the current period (set by filter) and then grabs the data from the same period, be it week, month, or quarter, from the pervious year. 

 

Thanks MUCH!

P.S. If anyone has a dynamic LY/TY chart or Dashboard they want to share, to aid my learning, I'd be most grateful too!

Discussion (4)0
Log in or sign up to continue
Alessandro Marin · Nov 9, 2016

Hello Samuel,

This is definitely not a newbie question, and ParallelPeriod could be very useful for the MDX you need. However, your pivot table has to be dynamic enough to work with different periods (Quarter, Month, Week). For this reason we might have to work on a KPI. I have two questions:

1) If I understand you correctly you want to be able to select any period (such as Jan-2014 or Week 2 of 2015), not only the current one (such as Nov-2016 or Q4-2016). Is that correct?

2) If so, what filters you like to appear on your dashboard? For example, would you like to have a dropdown menu to select Quarter/Mont/Week and a "dynamic" filter showing a Quarter/Month/Week for all years in your data?
 

1 0
Samuel Claiborne · Nov 14, 2016

In answer to both questions, yes. Ideally, I'd have drop downs for year/week/month in the dashboard and the user could pick from them, and then the Last Period would dynamically react to what they selected. Not sure exactly how I'd implement the user interface, as I am just starting to make dashboards. So, maybe a radio button for year/quarter/month/week, and then a dynamically-populated dropdown based on which radio button they select, or perhaps 4 dropdowns...  I do NOT foresee them picking last year 3rd quarter, and then giving them last year -1 3rd quarter - I more foresee only giving them options within this year - but that may not be true at all at the start of the year, when there's little data. I have to research it more.

I have seen the YTD and TOTALYTD functions referenced in MDX queries on the web, but I don't see these supported in DeepSee. It *appears* to me that these functions would make this a fairly easy  thing to implement at the yearly level, which, frankly, I could live with in the short term. Long term, they will want to see quarter-to-quarter performances, month-to-month, and week-to-week.

Thanks

0 0
Samuel Claiborne · Nov 14, 2016

I'm also not sure I phrased the initial question properly. It's period A - Period B to date - so: year to date, quarter to date, month to date, year to date - this year/last year or this quarter/last quarter etc. However, I will probably want to develop the ability to see 3rd quarter last year vs 3rd quarter this year too, eventually - so, yes, a period-to-date, and a discrete period as well.

0 0