Datazen comparison with values of one year before

Question

I am new with Datazen and trying some comparison options. Is it possible to compare values with that of one year before? All the data is available, so it seems unnecessary to copy the whole datatable and deduct one year of the date.

Answer 1

It is possible. However the calculation needs to be done in your SQL/MDX.

So you will need to have a "this year" measure/value and a corresponding "last year" measure/value.

Simply having all the data that you need in a dataset will not solve your problem as you can't do any sort of custom calculations in Datazen.

Answer 2

Below an example with MDX. In date field select the date that you want to compare with the parallel 1 year ago.

SELECT
{[Measures].[Internet Order Quantity]} ON 0
, {[Date].[Calendar].[Date].[March 22, 2004]
, ParallelPeriod([Date].[Calendar].[Calendar Year]
, 1
, [Date].[Calendar].[Date].[March 22, 2004]
)
} ON 1
FROM [Adventure Works]

Answer 3

Thank you for your answers. I solved it myself by making a new view with

DATEADD (year,1,usedDate)

Comparing these two views gives the right solution too.

Answer 4

If you want to compare two or more years in the same time chart I've hacked a solution for this earlier. I've made a column in my MDX that pulls out the year so I can use this as the series name column. Then if my data f.ex is monthly I've updated all the months to point to the same year. Meaning I have a column with three values for 1/1/2015, but in my series name column I have 2013, 2014 and 2015 so I can chart something like this.

It is a hack, but I've found it to be useful in some occasions.