Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Use of Expression Label in antoher Expression

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

6 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

How does the presence of variables affect this?

Thanks for your time.