Active Days in a Calendar where the Active Days varies over site...

[felix18807]

I'm looking for some best practice information for relating transactional data to a working day calendar for the purposes of calculating spend per day etc. The problem that I have is that the active days varies per site (i.e. some sites are open Sunday etc.).

Should I create separate calendars for each site (there are many!) or sit the information relating only to the site and do something clever in the expression or something else.

All advice gratefully received.

Many Thanks

Rich

[vgutkovsky · Accepted solution]

Rich,

I would just create a flag in your Fact table that would indicate via 1/0 whether each Fact record corresponds to a working day or not. You can then use this flag in set analysis.

However, if a site is closed on Sunday, why would you have Fact data coming through for that site in the first place? If you don't, then you can simply say something like:

sum(Sales)/count({<Sales={"<>0"}>}distinct Date)

where the dimension is Site.

Regards,

Vlad