Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an expression label called "Informal Hours" which calculates two timestamps difference in working hours. How can I use this label in the following exression?
=if(count(Day) > 2 , avg(if(Sales > aggr(NODISTINCT fractile(Sales, 0.1), Month) and Sales < aggr(NODISTINCT fractile(Sales, 0.9), Month),Sales)), avg(Sales))
use that header (only if there are not varibles in it) with squares: [Informal Hours]
Not sure where the timestamp difference fits into your expression, but you can use [Informal Hours] anywhere in your expressions.
Thanks Alessandro and Jacob,
I think i can use Sales with Informal Hours but what about Day and Month becuase my calculation is based on hours
Could you provide your qvw? I'm unsure of what you are trying to accomplish with your expression.
Jacob, I am afraid ca't upload qvw file. I am using the following expression to calcualte working hours. If you know better expression to calcualate two timestamps difference in hours based on Monday to Friday and 9am to 9pm please let me know. I will replace it then. In the following expression my application entered date is EnteredFirstTimestamp and decisioned date is AnalysedFirstTimestamp. I happy with this expression but there are outliers in the therfore i need to use the above (or whatever you suggest) to exlucde the outliers
avg(fabs(interval(((Networkdays(Date(EnteredFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date(AnalysedFirstTimestamp,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')
+
if(frac(date(EnteredFirstTimestamp))<num('$(vQuitTime)'),if(frac(date(EnteredFirstTimestamp))>num('$(vStartTime)'),Date#(date(floor(EnteredFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(EnteredFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(EnteredFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(EnteredFirstTimestamp),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)
+
if(frac(date(AnalysedFirstTimestamp))>num('$(vStartTime)'),if(frac(date(AnalysedFirstTimestamp))<num('$(vQuitTime)'),(Date(AnalysedFirstTimestamp,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(AnalysedFirstTimestamp),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(AnalysedFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(AnalysedFirstTimestamp),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss')))*24
How does the presence of variables affect this?
Thanks for your time.