Need help creating Relationship filter (7.2.2)

[MSIsam]

I have the need to create a filter for Last week, 2 weeks ago, 3 weeks ago, etc. Our week dimension table has a last week idicator so the filter for last week is straightforward. The week dimension table also has a field that is just a sequential number that increases by 1 for each week. I built a filter with applysimple logic that works, but is not a good strategic decision for maintenance purposes. Is there a way to build a relationship filter (or something else) to accomplish this? The code for the applysimple filter is as follows:
Filter-2 Weeks Ago

SEQ_NBR(ID) Exactly ApplySimple(
"(select SEQ_NBR-1 
  from DB.WEEK_DIM 
  where lst_wk_ind = 1)
",0)

 

[mstrNate]

I think your current filter logic looks fine, but it might need tuning depending on how you store the seq_nbr value. If seq_nbr is just 1-52, then what happens if you use this filter in week 1? If it really is a sequence from the first week in your table all the way to the last week in your table, then you wouldn't have a problem.

To reduce the number of filters you create, you could try prompting for a numeric value in a filter and use the numeric value to determine how many weeks back to go. Of course the drawback is being prompted each time to fill out the answer. Try the following out if you would like. I created an object prompt called [No Weeks Back] for a numeric value, then stuck that in an advanced qualification filter like so.

ApplyComparison ("#0 IN (SELECT seq_nbr FROM week_dim WHERE seq_nbr = (select seq_nbr - #1 from week_dim where lst_wk_idnt = 1))", SEQ_NBR@ID, ?[No Weeks Back])

Place that filter in a report, and your seq_nbr attribute on the report to see what it does.

HTH,
Nate

 

[MSIsam]

Thanks Nate,
I guess I was not clear enough in my explanation. My goal is to use the same logic to build a filter that does not have hard-coded SQL. I can't control some of the data model changes that occur here and it could me a maintenance issue if there are database changes.

Thanks,
Sam

 

[entaroadun]

MSIsam,

I assume that you have an attribute called "Last Week Indicator" and that your Last Week filter expression is "[Last Week Indicator]@ID = 1". Have I interpreted your model correctly?

Based on this, I can think of no solution that doesn't involve custom SQL or a data model change. I would work with the data modeler to come up with a solution that is viable over the long term. Filters aren't schema objects, but application objects that depend on the WH schema should be the responsibility of the data modeler, not you.

 

[MSIsam]

Thanks entaroadun,
You are correct, the LW indicator is set to 1 for last week. I was afraid that there would be no solution other than freehand SQL. I agree that the correct procedure for long term is to create the indicator columns but I was hoping to avoid the red tape.

Thanks Again,
Sam