topic User-specific date format in QlikView

User-specific date format

Anonymous — Fri, 15 May 2009 07:34:56 GMT

Forum,

Here is a problem. Client has users in multiple countries who are using the same QVS-based application. Date format is different from country to country, for example DD/MM/YY in Europe, and MM/DD/YY in US. They want to be able to see the data in their respective format.

Is there a reliable way to implement this? Appreciate any ideas, especially the crazy once - they help to think.

(I've implemented a clumsy solution based on time functions that worked OK in QVS 8.01, but behaves rather erratic in QVS 8.50. I reported a bug to QlikTech.)

User-specific date format

disqr_rm — Fri, 15 May 2009 10:41:59 GMT

How about having a variable at runtime (Settings -> Variable Overview), say vLang, and setting it to:

=getregistrystring('HKEY_CURRENT_USER\Control Panel\International', 'sShortDate')

This would give you the date format of the user in the vLang variable. Then use this variable in charts and everywhere to format the date.

User-specific date format

Anonymous — Fri, 15 May 2009 19:35:50 GMT

Rakesh,
Thank you for the idea. On the local machine it is more elegant than my current solution. The problem is that it doesn't work for server-based apps. User gets registry info from the server, no matter what is on the user machine. The is, if the serever date format is MM/DD/YY, and user setting is DD/MM/YY, yser gets MM/DD/YY.
(I tested with QVS 8.50 and 8.01)
But your idea may help to find a working solution. I'm going to try around it.

User-specific date format

Anonymous — Fri, 15 May 2009 19:58:52 GMT

There is a solution. The important part is the Date Format variable must be assigned on open, that means the expression cannot be used in the variable definition. I'm going to use this macro:


sub OnOpen
 ...
 DateFormat
 ActiveDocument.GetApplication.WaitForIdle
 ...
end sub
private sub DateFormat
 df = ActiveDocument.Evaluate("=upper(getregistrystring('HKEY_CURRENT_USER\Control Panel\International', 'sShortDate'))")
 ActiveDocument.Variables("vDF").SetContent df, true
end sub

Thanks a lot Rakesh!

User-specific date format

disqr_rm — Fri, 15 May 2009 20:07:55 GMT

Michael, That's pretty smart.

Somehow, I have feeling that the variable solution also should work on Server & IE combination of application. that's is why I was checking the user specific keys and not the system default one. But to be honest, I have never tried it myself. Anyway, we know hoe to handle it now.

Thanks for posting the final solution.

User-specific date format

Anonymous — Fri, 15 May 2009 20:31:35 GMT

Rakesh,
Sorry to dissapoint, but it didn't work anyway. I apparently missed something in my testing, likely was opening a local copy of the file instead of server-based.
It consistenly reads info from the server registry. Even if I run the above "DateFormat" macro using a button on the user machine, it gets data from the server.
To be continued, I think...

User-specific date format

disqr_rm — Fri, 15 May 2009 21:50:34 GMT

Try this in Macro (OnOpen?):

set v = ActiveDocument.Variables("vDateFormat")
v.SetContent ActiveDocument.GetProperties.LocaleInfo.DateFmt, true

This would give you user date format in the vDateFormat variable. Don't forget to create that variable first with empty value.

This is interesting. Keep me updated.

User-specific date format

Anonymous — Fri, 15 May 2009 22:20:47 GMT

Thanks Rakesh,
I tried this now (and also "GetLocaleInfo"), and the result is nor from local user neither from server, but rather from the application format settings
SET DataFormat =...
This is defined on reload. If there is no DataFormat variable, it will be taken from the system where the reload runs

User-specific date format

disqr_rm — Fri, 15 May 2009 22:34:43 GMT

Damn! This should be easy.

The last thing I can think is to create a function (say, GetMeUserLocale) which uses VBScript function GetLocale() and returns this as string value.

Then, have a table (load Excel, or something) in QV with LocaleID and DateFormat which will be filtered (or searched) based on the return value of GetMeUserLocale.

I have dealt with this situation in a real environment, but did not solve it by getting system locale, as my user could be a US user sitting in Japan still wants to see dates in US format. What I ended up doing is to have a centralized user preference management ASP application, where you can set your prferences (decimals, colors, totals, dates, etc.) for all QV Applications and this ASP app stores this information in an SQL Table. Ofcourse the key is QV Application ID assigned uniquely to each productive QV application. Then, this SQL table is loaded in every QV document (WHERE AppID = QV_App_Name) and then section access reduces this UserPref table to the accessing user only (AD User ID). This worked just fine for me in the past.

User-specific date format

Anonymous — Sat, 16 May 2009 00:57:16 GMT

Rakesh,
Believe it or not, we got it!
It turned out, that VBscript functions get info from the user box, unlike the QV functions that read the server. So it's basically the same as above, but I'm using VBscript to get the format from the regisrty. Well, a few lines longer:


sub OnOpen
 ...
 DateFormat
 ActiveDocument.GetApplication.WaitForIdle
 ...
end sub
sub DateFormat
 const HKEY_CURRENT_USER = &H80000001
 strComputer = "."
 Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
 strComputer & "\root\default:StdRegProv")
 strKeyPath = "Control Panel\International"
 strValueName = "sShortDate"
 oReg.GetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue
 df = UCase(strValue)
 ActiveDocument.Variables("vDF").SetContent df, true
end sub

I hope I didn't miss anyhing this time. Your hints really helped, thanks.

User-specific date format

disqr_rm — Sat, 16 May 2009 01:17:02 GMT

oh! man. I was just wondering what I would suggest next if you say "nope, didn't work". 🙂 Glad it worked.

Thanks for your confirmation.

I think this thread deserve to be a Wiki.

User-specific date format

Anonymous — Sat, 16 May 2009 01:58:04 GMT

Done, it is on Wiki now, thanks

User-specific date format

rwunderlich — Sat, 16 May 2009 02:46:28 GMT

Michael,

Nice solution. The only downside I see is that the macro requires system access. Would it work to do it this way, which does not require system access?

sub DateFormat
df = ActiveDocument.Evaluate("getregistrystring('HKEY_CURRENT_USER\Control Panel\International', 'sShortDate')")
df = UCase(df)
 ActiveDocument.Variables("vDF").SetContent df, true
end sub

-Rob

User-specific date format

Anonymous — Sat, 16 May 2009 02:51:15 GMT

Rob,
If you look up the thread you'll find almost exactly this...
The problem is that it gets the registry info from server, not from user.
Client will have to leave with the system access if they really want the feature.

User-specific date format

Anonymous — Sat, 16 May 2009 02:58:59 GMT

BTW, I've noticed earlier that the QV now() function returns server time instead of user on the server-based apps (if they are different), but VBscript "now" always takes it from user. I had to use this:


private sub vNow
 n = CDbl(now)
 ActiveDocument.Variables("vNow").SetContent n, true
end sub

Looks like a trend...

User-specific date format

rwunderlich — Sat, 16 May 2009 03:33:12 GMT


Michael Solomovich wrote:If you look up the thread you'll find almost exactly this

Michael,

No, I believe I am suggesting something different. Rakesh suggested using the QV GetRegistryString() function in a variable expression.

I'm suggesting using the QV GetRegistryString() function in the OnOpen macro VBSCript. You should then be executing in the user's machine -- just like your other example. Except without System Access required.

-Rob

User-specific date format

Anonymous — Sat, 16 May 2009 03:41:04 GMT

That's correct Rob, Rakesh suggested it in the variable definition, but I actually tried it by OnOpen event exactly because of the same reason you're thinking of. Moreover, I even tried to attach it to a button to be able to run it directly in the application on any box, user or server - and it returned server info in any situation.
I'll be happy if you prove me wrong in this case...

User-specific date format

rwunderlich — Sat, 16 May 2009 03:45:43 GMT

Ok, I'm guilty of incomplete reading. I see you had already tested and reported the failure of exactly what I suggested. I can't make it work either.

-Rob

User-specific date format

Anonymous — Sat, 16 May 2009 03:48:41 GMT

Thanks for testing...

Re: User-specific date format

jfkinspari — Wed, 07 Jan 2015 16:13:09 GMT

Hi Michael,

Can you confirm that this method actually returns the users local settings, as my attempts seems to return the servers regional settings?

Or perhaps you solved the quest in another way?

Thanks!