6 Replies Latest reply on Jul 1, 2013 1:07 AM by Elena Barmina

# Is there a way to compare data of the selected period with the previous period?

Hello,

I would like to create a chart that will show comparison of a metric  value for the selected period with the value for the previous period, the period being WEEK.

I think I want it as a bar chart where the bars will show the  value for the selected week (selected by means of a date filter showing week numbers) and I would like to show the previous week's value to be displayed as a reference line. Or it could be a table with just two sets of values: for the selected week and for the week previous to selected.

Suppose my metric is "Number of Items Processed". In order to do the comparison I was thinking of creating a calculated field that would be looking at the previous week's data.

One variant that I thought of was to use the WINDOW_AVG function and create the following calculation:

WINDOW_AVG(SUM([Number of Items Processed]), -8, 0).

Not sure this is the optimal way, though.

Can anyone give any recommendations or point me to any related topics that you've come across?

My question is:

1. How can I create a calculation that would be looking at a previous time period? The condition is that if the time period of the report is changed (for example, from week 5 to week 6 or other week), the calculation will be looking at the previous period related to the selected period.

And also a related question:

1. Is there a way to create a calculation that would always be looking at the same date? For example, "Number of Items Processed in Week 5 of 2013"? (I can see there's a YEAR function is the list of Date calculations in the Calculated Field dialog, but only YEAR. Is there a way to get dates of smaller granularity, e.g. WEEK?)

Sorry if these are the irritating newbie questions. I haven't manages to find a case description on the forum I'd be able to use straightforwardly. So if you know there is one, just point me there.

Thanks!

• ###### 1. Re: Is there a way to compare data of the selected period with the previous period?

The easiest way to do previous week is to put a week date on the row shelf and use a quick table calculation of 'difference' to compare to previous week. It writes the formula for you:

ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)

It's a lot more complicated than that, but it gets you started. (See attached.)

Cheers,

--Shawn

• ###### 2. Re: Is there a way to compare data of the selected period with the previous period?

Hi Shawn,

Yes, I'm aware of the Difference calculation.

What I would like to do, though, is to show the actual value for the previous period side by side with the value for the selected period. And when the report time range is switched I'd like that "previous" to be changed accordingly.

Thanks.

• ###### 3. Re: Is there a way to compare data of the selected period with the previous period?

Elena, then just use the second half:

LOOKUP(ZN(SUM([Sales])), -1)

--Shawn

1 of 1 people found this helpful
• ###### 4. Re: Is there a way to compare data of the selected period with the previous period?

Yeah... Could have figured out myself. Thanks!

• ###### 5. Re: Is there a way to compare data of the selected period with the previous period?

Did you find a solution? I'm trying to do the same for my data.

• ###### 6. Re: Is there a way to compare data of the selected period with the previous period?

Hi,

Please see recommendations above - use the table calculation Difference from Previous  - calculated on Date. I did so and then saved the calc as a formula to be used independetly in reports.

Thanks.