2 Replies Latest reply: Nov 15, 2011 4:39 AM by eliaseuri RSS

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

      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