Combining Expressions

[wonkymeister]

Hi all,

Happy Friday!

I have an employee data model that is made up of lots of fact tables. There are 2 main fact tables, which every employee will always exist in, and then many other tables that hang off them where employees may or may not have a record. Each table has a begin and end date field for each record, and a record is current where the end date is set to ‘01/01/9999’.

My base set analysis in the expression is:

=Count({$
<
Table_1_BeginDate = {"<=$(vTodayDate)"}, (this is 1 of the 2 main tables)
Table_1_EndDate   = {">$( vTodayDate)"},
Table_2_BeginDate = {"<=$( vTodayDate)"}, (this is 2 of the 2 main tables)
Table_2_EndDate   = {">$( vTodayDate)"}
>
}  DISTINCT EmployeeID)

This will return the true employee headcount when used on either of the 2 main tables. The problem I have is when I need to get the headcount from another table in the model. I have to introduce the start date and end date of that particular table to the expression which changes the headcount total. I.E. i may not get the same headcount as all the employees from the two main tables may not exist in another table.

Now this is fine for standalone objects on the dashboard (dimensions from one table only). But if I want to have many dimensions from many tables in one object the headcount is incorrect.

How can I include the BeginDate and EndDate in the expression from every table

Or

To put it simpler, how can I ensure I always bring back the true headcount from the 2 main tables (ie count the nulls) no matter what table I count from?

1. Sorry, I hope this makes sense. It’s a big SAP schema, and its driving me mad J

Thanks all.

[#2]

Hi

i'm just giving you a clue because it seems difficult to answer basically to a complex model.

May be you should create a MaseterCalendar table which will link with other tables in your model thru

"Date & <KeyField>"  as a key

you then lighten your set analysis to date from calendar table

best regards

Chris

[wonkymeister]

cheers Chris, thanks for the reply.

i do use a master calendar which is dissociated from the set allowing dynamic selections. The main issue is NULLS. Its almost like scripting a left outer join in set analysis.

i cant count from two different tables with just one expression due to the start/end dates.

i never, ever want to touch a SAP schema again. Ever.