How to display expressions using different date fields with a single dimension

[#1]

Hi,

I'm trying to recreate the following report to begin and then display the datas in a chart.

	October	November	December	January	Total
Number of  loss time accidents (expression 1)	XXX	XXX	XXX	XXX
Number of lost days for the month (expression 2)	YYY	YYY	YYY	YYY
Number of worked hours for the month (expression 3)	ZZZ	ZZZ	ZZZ	ZZZ

My problem is that each line contains data retrieved by an expression and also uses a different date field, and I need to display these data agregated by month in a single pivot table.

As you can see in the data model, I created a calendar table to help me, but when I try to use it in a dimension for the three expressions, I'll get everything wrong.

Here are the 3 expressions (currently used in 3 different pivot tables and as a matter of fact 3 different dimensions):

Number of loss time accident:
=Count ({$<CASETYPE_ID={'20'}, INJURY_CLASS_ID={1, 2, 3, 4}, CASE_DATE={">=$(=min(Day))<=$(=max(Day))"} >}distinct PERSONAL_INJURY_ID)

Number of lost days for the month:
Sum(Aggr(Count({$<CASETYPE_ID = {'20'}, COMPANY_ID=P(COMPANY_C), UNIT=P(RESP_UNIT), LTADay={">=$(=min(Day))<=$(=max(Day))"}>}distinct LTADay),PERSONAL_INJURY_ID))

Number of worked hours for the month:
Sum ({$<CASETYPE_ID={'83'}, WORKED_HOURS_START={">=$(Day)"}, WORKED_HOURS_END={"<=$(Day)"} >}WORKED_HOURS_VALUE)

How can I display these 3 expressions in a single pivot table? Is it possible to define a single dimension that would work with the 3 expressions? Do I need to modify the 3 expressions? Do I need to modify the data model?

I hope you can help me, any ideas to get myself back on track would be great , and I thank you in advance for considering my question.

David

[swuehl · Accepted solution]

Hi David,

I assume you expect the Day in your set expression to be limited to the appropriate values according to Month dimension and then set the different date fields to these values. I think this is not going to work, since set analysis will not take the current dimension into account. You probably get the same values in each row, a total not regarding the Month.

You could combine your set analysis with an if()-clause that will regard the current dimension:

=Count ({$<CASETYPE_ID={'20'}, INJURY_CLASS_ID={1, 2, 3, 4} >}

distinct if(CASE_DATE>=MonthStart and CASE_DATE<=MonthEnd, PERSONAL_INJURY_ID))

Note that I used fields MonthStart and MonthEnd that doesn't exist in your table yet. You could easily add them with

monthstart(DATE) as MonthStart resp. monthend(DATE) as Monthend in your load script, or maybe you already have fields which hold the same content (MonthYear?).

If you want to use min(Day) or max(Day), you will probably need to add advanced aggregation, since you are not allowed to use an aggregation function like min() inside another like count().

Hope this helps and works out,

Stefan

[#3]

Hi Stefan,

Sorry for the late reply, I had to modify the datamodel to avoid using the "Sum(Aggr(Count" in the second expression, but finally it works, and I found a way to improve the data model.

I experienced a weird thing with two of the new expressions. When I moved the condition on the date from the set analysis to the if() clause, my results were multiplied by the number of days by month. Here is the expression for the number of lost days:

=Count ({$<CASETYPE_ID = {'20'}, COMPANY_ID={'1'}, UNIT=P(RESP_UNIT), INJURY_CLASS_ID=P(INJURY_CLASS_C)>}
  if(LTADay>=Monthstart(Day) and LTADay<=Monthend(Day), LTADay))

So I solved my problem like this:

=Count ({$<CASETYPE_ID = {'20'}, COMPANY_ID={'1'}, UNIT=P(RESP_UNIT), INJURY_CLASS_ID=P(INJURY_CLASS_C)>}
  if(LTADay>=Monthstart(Day) and LTADay<=Monthend(Day), LTADay))/count(Day)

Once again you were very helpful. Thank you.

David