topic Re: Use of Expression Label in antoher Expression in QlikView

Use of Expression Label in antoher Expression

Mon, 09 Jun 2014 15:28:47 GMT

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))

Re: Use of Expression Label in antoher Expression

alexandros17 — Mon, 09 Jun 2014 15:31:56 GMT

use that header (only if there are not varibles in it) with squares: [Informal Hours]

Re: Use of Expression Label in antoher Expression

Mon, 09 Jun 2014 15:35:06 GMT

Not sure where the timestamp difference fits into your expression, but you can use [Informal Hours] anywhere in your expressions.

Re: Use of Expression Label in antoher Expression

Mon, 09 Jun 2014 15:38:30 GMT

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

Re: Use of Expression Label in antoher Expression

Mon, 09 Jun 2014 16:07:35 GMT

Could you provide your qvw? I'm unsure of what you are trying to accomplish with your expression.

Re: Use of Expression Label in antoher Expression

Mon, 09 Jun 2014 16:16:18 GMT

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

Re: Use of Expression Label in antoher Expression

Wed, 24 Sep 2014 23:01:14 GMT

How does the presence of variables affect this?

Thanks for your time.