Reported ill more than twice over last 12 months.

[#1]

I am struggling with the next challenge:

I would like to count everyone reported ill more than twice over the previous twelve months.

One should be able to select the period. If more than one month is selected, the highest month can be selected.

To count everyone reported ill I solved with the next formula in the chart.

sum({$<Year=,Month=,Period_Num = {">=$(vRolingMonth_Start) <=$(vRolingMonth_End)"}>}ZV_First_DayIll_Counter)

The variable vRolingMonth_End is filled with the max(Period) applicable.

The variable vRolingMonth_Start is filled with the formula addmonths -12.

This works for counting all reported illnesses.

This has to be shown in a Gauge chart, because I would like to know the percentage of those reported ill often as part of the total of reported illnesses.

Anyone an idea?

[RedSky001]

The way I did this was to have absences as SCD  (Slowly Changing Dimension) type 2 

(Google will explain it better than I can).  The fact is a snap shot in time.

Here's an example of the  absences dimension table, (simplified)

%keyAbsence	Start Date	End Date	Person
1	08/01/2012	09/01/2012	Mark
2	15/07/2012	20/07/2012	Mark
3	14/09/2012	15/09/2012	Sarah

I used intervalmatch() to match start and end dates to a  master calendar, allowing me to create the fact table.

You can then count users by their absences.  So in your scenario create a chart with person as the dimension and an expression asIF(COUNT(DISTINCT %keyAbsence)>2,1)

he user can just select their own start date and end date..

Hope that is of some help if not It would be helpful if you could upload a photo of your data model or even upload the actual dashboard (but I'm guessing this is sensitive data, so probably not an option!)

Mark