Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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