Variables and Date representation, number presentation

[jonoogle]

Sometime, in some evolution of QlikView desktop something has changed in respect to variables and dates. We use a slider/calendar object to set a variable. We have various apps that were developed over the years which use this standard technique. When examining the variables they are in some readable date format, like mm/dd/yyyy, NOT the number of days after 12/31/1900.

Recently we created a new variable in one of our apps, and the variable displays the date in # days and not in date format. After much experimentation I found that with pre-existing "date" variables, created in older versions (IDK which version anymore) everything was displayed in date format; but with newly created variables, this is not the case.

We resolved this issue with a simple macro, which I wanted to share, which only needs to be run one time after variable creation. (obviously change the name of variables according to your app. This one "fixes" two variables (varMinDateInput and varMaxDateInput)

sub setVariableType(varName, numberPresentationType)
		set  varObj = ActiveDocument.Variables(varName)
		set prop = varObj.GetProperties
		prop.NumberPresentation.Type = numberPresentationType
		varObj.SetProperties prop
end sub

sub setVariableTypes()
	call setVariableType("varMinDateInput",3)
	call setVariableType("varMaxDateInput",3)
end sub

[hic]

Variables are Duals. This means that they can have BOTH the date as a readable string and the number of days from 12/30/1899. So, it depends on the variable definition what you have. And it depends on the formatting what you see.

See also https://community.qlik.com/t5/Qlik-Design-Blog/Data-Types-in-QlikView/ba-p/1474977

HIC

[jonoogle]

Hi HC,

I think if you read what I wrote above, you would see that I understand about dual representations and I also understand about formatting. 

First, I did not want to specify the format when referring to a variable in expressions/dimensions or the variable definition itself, rather just use the variable. 

Second, I have apps created with older versions of Qlik where the variable representation for the date is in the mm/dd/yyyy format. I did not do anything to make this happen. That was somehow the default representation and it just worked. When I create new variables in the same app, they no longer have this representation.

Third, maybe I am ignorant, but your comment "it depends on the variable definition what you have" makes me think that there is some documented way that you can actually specify a variable definition, i.e. it's dual representation type. I do not find that in documentation or in the community.

Fourth; that was the purpose of my code above, to change the representation of the variable.

If you have another way to change the variable definition via Desktop UI, let me know.

thanks

Jon

[hic]

My comment on Duals was caused by your comment on that the variables are "in some readable date format, like mm/dd/yyyy, NOT the number of days after 12/31/1900." Variables usually have both representations, but sometimes just one of the two - here the numeric part.

So if you define a variable in the script, it will get both representations:
Let DateVar = Date(42345,'M/D/YYYY');

But if you change the value of the variable using a slider in the UI, it will just get the numeric part. So after the script run it looks like

and after a change in the slider, it looks like

Note that the format in the input box has changed. The format of the slider is set in the slider properties.

[jonoogle]

Hi Henric,

So maybe I can show you something then! (which was the point of my original post)

Take a look at the example I am attaching. Before running the macro, as you stated, the app functioned as you said. Once I move the slider the date variable changed into numeric format. 

Take a look at the attached app. I have two variables, both set like in your example. On one of the variables, "DateVar", I ran the macro one single time. No need to run it multiple times. This has "permanently" changed the variable presentation type to a date.  The second variable was not run with the macro. The result is that the "macroed" variable will ALWAYS display in mm/d/yyyy format, while the non-macroed variable will revert to numeric. 

And as a note, as I mentioned in my original post, in some way, it looks like older versions of Qlik desktop deferred to this presentation type. I am not sure how, but newer versions, do not.

Jon

[hic]

Interesting! 

And, yes, I learned something new. 

HIC

[marcus_sommer]

I think it's caused from a missing format-setting within the input-box. If I change the variable-values with the slider without adjusting the format it behaved like you mentioned but if I set the DateVarNotMacroed within the tab number to a date - it remained a formatted date after using the slider.

- Marcus

[jonoogle]

Hi Marcus,

I am not understanding.  In the example DateVarNotMacroed is a date and if you slide it the variable is displayed in an integer format. Of course I could reformat the variable wherever I want to display it, but I wanted to avoid that. I wanted the variable itself to be in date format. This all worked in older versions of Qlik desktop. It no longer works in newer versions, but the macro fix that I used in the example does change it so the variable is always displayed as a date. If you clarify what you mean by "set the DateVarNotMacroed within the tab number to a date" and how that is different than specifying "date" as the format pattern for the object, I would be interested in knowing,

thanks

[marcus_sommer]

I just took your example and opened it and here the variable DateVarNotMacroed isn't formatted as date within the inputbox - I didn't touched the slider-properties. If I then use the slider the variable lost the formatting but if I set the variable to a date the formatting remained. I didn't a reload or changed anything else. Therefore it worked for me like it should.

My test was with 12.5 SR3 - which release is yours?

- Marcus

[jonoogle]

Hi Marcus,

This is very interesting. I wasn't using input boxes for these variable and just  used them in this example to  demonstrate the variable contents. In my original app, there were no input boxes and the dates were used without formatting in expressions. There was no way I found to set a default output format without actually formatting the variable.  When I modified the app after many years and added a new variable  I found newly added variables  no longer kept the format like the others did, so I tested with the macro and it seemed to have resolved the issue.

But I see from your example, as you have demonstrated, setting the type in the input variable has the same effect.

Thanks so much for replying. This is a much better solution than mine.

Jon