Date Lookup Table

[talenx]

Hello all,
I have been looking all over the net for advice or examples of how to create the ability to filter data on a reverse calendar basis.

I guess I could explain this better . I have a lookup table call lu_time and it has the following fields in it

date_key, datenbr, daydesc, week_key, period_key, year_key.


the date_key is a int
datenbr is a char 8
datedesc is a datetime
week_key is a int
period_key is a int
year_key is a int


the issue I am having is I need to be able to return a dataset that shows week1 week2 week3 week4 on only one metric value.

the week4 would be the end of the date range or selected period.

so the following dataset would be displayed in the week fields if the report had the following criteria selected

period = period 2
which incases week5, week6 week7 week8 id's

the report would show

week1= week5 data
week2= week6 data
week3= week7 data
week4= week8 data

REPORT
salesrep|sales/week1|sales/week2|sales/week3|sales/week4|othermetrics

207 -- 100 --200 --100 --900 78
207 -- 300 --220 --110 --900 76
207 -- 130 --222 --110 --400 98
207 -- 200 --240 --150 --800 90

I tried to utilize the transformation option but it only allowed me to show ytd for the current selected criteria or last week.

any suggestions?

thanks
talenx

 

[nlim]

not too hard, the trick is you need to create 4 filters, one for each week.

Let me explain one way to create one, and you can extrapolate to the others. It assumes that weekkey increases incrementally. ie. in period 2, the id for week5 is smaller than that of week6 and so on.

1) create period and week attribute. Make period parent of week, one-to-many relationship
2) create a metric M1 defined as runningcount(max(week attribute)).
3) create a filter F1 that contains your time period prompt.
4) create a report R1 with F1, week and M1 on the template.
5) for R1, add another filter condition M1=1. When you run this report, it should return the first week in your time period.
6) Create filter called "firstweekfilter". Drag R1 into this filter.
7) Now create your "sales/week1" metric, and add "firstweekfilter" to this metric.

For week 2, repeat steps 4 - 7 with different names, and set the filter in step 5 to be M1=2.

Same for week3 and week4. Now add the 4 filtered metrics to your report.