Dynamic Filter for 2 Business Days Back.

[10161977]

Hi,

I am trying to create a dynamic filter on a date Attribute to calculate 2 business days behind. For example if today is Monday then I want MSTR to calculate Thursday and so on.

Any help is greatly appreciated.

Thanks,

 

[FLB]

Build a relationship filter:

Set of Days where (My 2nd Filter)

Define "My 2nd Filter" as:

My Rank Metric <= 2

Define "My Rank Metric" as:

Rank(ApplySimple("My Expression", Day@ID))
filter = dynamic filter last 4 days

With "My Expression" something you will have to find out yourself for your database. If will look like this:

Case When DayOfWeek(#0) = 1 then To_Date('01/01/1900') When DayOfWeek(#0) = 7 then To_Date('01/01/1900') Else #0

[The goal is to get rid of the Sat & Sun]

Of course..... If you have to handle holiday,... You are better off putting a flag in your date table to flag the "business days" and replace the filter
dynamic filter last 4 days
By
dynamic filter last 7 days AND BusinessDay = 1
[In which case, you don't need the applysimple anymore]

2 cents,
FLB

 

[10161977]

FLB,

Thanks a lot for the prompt reply.

I could get everything but the Relationship Filter Part. When you said "set of days" in the "Advanced Qualification" does the "days" refer to the "Day Attribute".

Please let me know.

Once again, thank you very much

 

[10161977]

Hey FLB,

I tried to build the rank metric as you had asked me to but there is a minor problem here.

MSTR does not allow me to put an Attribute in a case statement in the metric editor.

Any ideas if there is a workaround for this.

Thanks,

 

[reisw]

Just wanted to give my two cents. We have had a similar requirement in the past but found performance to be a problem when implenting it this way. Our database is pretty large though so it could work fine in your environment. What we ended up doing was creating filters that get updated through command manager when ever we load our database. It reduces the amount of sql passes and usually results in better db performance.

 

[FLB]

I implemented this in tables with over a billion rows and never ran into any kind of performance.... You might want to check how your database server handles your query (explain plan).

The metric editor does not let you put an attribute, but it will let you put a attribute form! instead of just [Your attribute], put [Your attribute]@[your form name here].

Yes, the "Set of days" refered to the Day attribute (the filter editor will show [Set of 'Day'], so... I made it a little more English! ;-) ) Sorry....

HTH,
FLB

 

[10161977]

Hey FLB,

Attached is the expression in my "Rank Metric". I forgot to mention before, that my Database is SQL Server.

Please check the syntax and see if I am still doing something wrong. When I try the statement below, I still get the error "Object of (type: Attribute) not allowed in this place".

"Rank Metric" = Rank(ApplySimple( "Case When datepart(dw,(#0)) = 1 then cast ('01/01/1900' as datetime) When datepart(dw,(#0)) = 7 then cast ('01/01/1900' as datetime) Else #0 end", [Expenditure Date]@ ID))

Where "Expenditure Date" is my "Day" Attribute.

I really appreciate your help so far. If you can bail me out of this...it will be great.

Thanks,