Wednesday, 9 July 2014

Set analysis that ignores all selections except a few

How do you get Set Analysis to ignore all selections except for a few fields?

We have a calendar table, linked to a fact table and some selections are made.

We now want to work out a calculation based on the date range selected (eg WTE) - but there is a problem. As the data is linked, our non-calendar selections (in this case employee) are only linked to some of the dates - infact for 2014 only in the month of May!

So any calculations based on number of days, etc will only return what is in the data. Furthermore, using the {1} set analysis to ignore all selections will ignore also the selection in year, but we need that. And what if you have hundreds of fields to list?