Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

String Function in Set Analysis

My NT ID comes in the form of NTLOCAL\john.smith

I need to change time based on the logged in user. Each user's GMT Offset is stored in the database. When I hard code the value, I get the correct value:

=sum({<[DB User Name] = {'John Smith'}>} [GMT Hour Offset])

The below string also gives the correct value:

=trim(capitalize(replace(mid(OSUser(),9),'.',' '))) gives a value : John Smith.

However, the below set analysis while correct in syntax, does not give the correct value:

=sum({<[DB User Name] = {$(=trim(capitalize(replace(mid(OSUser(),12),'.',' '))) )}>} [GMT Hour Offset])

Where are I going wrong? Any better suggestions?

Thanks,

Dinesh.

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi Dinesh,

Check that the result of the function is quoted as a literal:

=sum({<[DB User Name] = {'$(=trim(capitalize(replace(mid(OSUser(),12),'.',' '))) )'}>} [GMT Hour Offset])


EDIT: Ok, there may be some quotes missing. What about creating a new table (say inline) where you store in a field the offset value for each user? Then, your expression will look like

=sum([Time Field] + [GMT Hour Offset])


I'd give it a shot, since once the user has logged, the value is not going to change.

Hope that helps

View solution in original post

4 Replies
Miguel_Angel_Baeyens

Hi Dinesh,

Check that the result of the function is quoted as a literal:

=sum({<[DB User Name] = {'$(=trim(capitalize(replace(mid(OSUser(),12),'.',' '))) )'}>} [GMT Hour Offset])


EDIT: Ok, there may be some quotes missing. What about creating a new table (say inline) where you store in a field the offset value for each user? Then, your expression will look like

=sum([Time Field] + [GMT Hour Offset])


I'd give it a shot, since once the user has logged, the value is not going to change.

Hope that helps

Anonymous
Not applicable
Author

That was it. For some reason, the editor shows a syntax error when the quotes are included, but it gives the correct results.

Dinesh.

Anonymous
Not applicable
Author

When I use your formula =sum([Time Field] + [GMT Hour Offset]), I get 40499.416666667. How do I format it to the right date time format?

Thanks,

Dinesh.

Miguel_Angel_Baeyens

Hi Dinesh,

Use the following:

=Date('40499,416666667', 'DD/MM/YYYY hh:mm:ss')


It will return "17/11/2010 10:00:00"

Hope that helps