Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In QlikSense I have a variable in the loadscript that defines a default date:
LET vDateDefaultFrom = Date(Addmonths(Now(),-20),'MM/DD/YYYY');
The variable value appears correctly as 02/25/2017 in the variable window but when I add it to a text object or KPI it is appearing as:
3.9662865642043e-05
If I wrap the variable in a Date() it is returning an incorrect date:
Date($(vDateDefaultFrom)) = 12/30/1899
Do we know why this is happening?
Hi Brian, try with simple quotes to avoid the division (02 divided by 25 divided by 2017) :
Date('$(vDateDefaultFrom)')
May be leave it as a number in the script
LET vDateDefaultFrom = Num(Floor(Addmonths(Now(), -20)));
and then use Date() on the front end
Thanks for the suggestions. I just took the variable out of the QVS and created it directly in the app. Not sure how this will affect performance but at least it is correctly appearing as a date.
UPDATE: Spoke too soon. Putting the variable in the app works as long as I do not put an '=' in front of the expression. In addition, another variable I created is not formatting dates correctly.
IF(COUNT([Transaction Month])=COUNT({<[Transaction Month]=>}[Transaction Month]),$(vDateDefaultFrom),MIN(TranDateTime))
When it returns $(vDateDefaultFrom) it is correctly formatted as 02/25/2017. When it returns MIN(TranDateTime) I am getting 41762.668055556.
If I create a text box with MIN(TranDateTime) it correctly displays as 05/03/2014 4:02:00PM.
I am struggling to understand the point of variables if these issues occur.
Final attempt appears to be working correctly:
IF(ISNULL(GetFieldSelections([Transaction Month])),Date(Addmonths(Today(),-20),'MM/DD/YYYY'),DATE(MIN(TranDateTime)))