How to dispatch the number of days from periods by month or year

[#1]

Hello,

I'm new to QV and until now I found solutions by reading you, but I'm now stuck.

I need to dispatch the number of lost days (injured employees) by month and by year in a pivot table, and I wondered is there was any magical function that could do that ?

To define the period of lost time caused by each injury, I have two fields INJURY_LTA_DATE_BEG and INJURY_LTA_DATE_END, and I wih I could aggregate the number of lost days by month.

I wrote an expression, that works only if I put the case number as a dimension in addition to years an months. If I don't, I'll get no results. I also tried to force the aggregation with aggr but it didn't worked better.

if(min(S_Date)<=INJURY_LTA_DATE_BEG AND max(S_Date)>=INJURY_LTA_DATE_BEG AND min(S_Date)<=INJURY_LTA_DATE_END AND max(S_Date)>=INJURY_LTA_DATE_END, (INJURY_LTA_DATE_END) - (INJURY_LTA_DATE_BEG) + 1,0)
+ if (min(S_Date)<=INJURY_LTA_DATE_BEG AND max(S_Date)>=INJURY_LTA_DATE_BEG AND NOT(min(S_Date)<=INJURY_LTA_DATE_END AND max(S_Date)>=INJURY_LTA_DATE_END), (max(S_Date)) - (INJURY_LTA_DATE_BEG) + 1,0)
+ if (NOT(min(S_Date)<=INJURY_LTA_DATE_BEG AND max(S_Date)>=INJURY_LTA_DATE_BEG) AND min(S_Date)<=INJURY_LTA_DATE_END AND max(S_Date)>=INJURY_LTA_DATE_END, (INJURY_LTA_DATE_END) - (min(S_Date)) + 1,0)
+ if (INJURY_LTA_DATE_BEG<min(S_Date) AND INJURY_LTA_DATE_END>max(S_Date), (max(S_Date))-(min(S_Date)) + 1,0)

I hope you can help me, and I thank you in advance for considering my question.

David

[swuehl · Accepted solution]

I recommend having a look into the QV cookbook, example 'counting days in transaction using IntervalMatch':

http://robwunderlich.com/downloads/

I think this should solve you problem (with a different approach, but that should be ok?).

Hope this helps,

Stefan

[#3]

Hi Stefan,

The website is blocked by my company firewall, but I downloaded it from home.

It works perfectly, and a lot faster than my former expression.

Thanks a lot.

David