How to achieve previous week data.

Chandra kanth

Hi,

I have a requirement where in the report have the following columns.

Product, Sales, Current Week sales, Previous week sales

===================================

XYZ     1000    34 (week sales)     33(week sales)

RST      1200    45 (week sales)     44(week sales)

I am unable to write a query to get the  previous week sales column data. Please advice.

Regards,

Chandra kanth.

Joel Acha

Hi @Chandra kanth

As long as you have a properly modelled time dimension and hierarchy, you should be able to achieve this using the AGO function and the Week level of your time dimension.

DanPryke

Hi @Chandra kanth,

I'd echo what @Joel Acha said about your time dimension.

1. Make sure that at each level of your time dimension hierarchy you have a unique number version of the week in the database i.e. 201605 and use this as your chronological key

2. Look at the content level of your fact table logical source against the time dimension, make sure it's pointing at the right level (most likely Day or Detail)

3. Attempt in the front end a formula like AGO("Fact Sales"."Current Week Sales", "Dim Time"."Time Hierarchy"."Week",1)

These should be the steps you need.

If there's any more detail you could provide that'd be great.

Thanks