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

Date nightmare

So I want to do something super easy, but it won't do what I want.

I just want to substract n days from a date.

First try :

SET vMaxDate = date(max({1} PivotDate ), 'DD/MM/YYYY');

SET vMinDate = $(vMaxDate) - 180;

Result :

$(vMaxDate)=28/07/2015

$(vMinDate)=42033

The substraction has occured, but why is vMinDate no longer a date but a date#?

Second try:

SET vMaxDate = date#(max({1} PivotDate ), 'DD/MM/YYYY');

SET vMinDate = $(vMaxDate) - 180;

Result :

$(vMaxDate)=42213

$(vMinDate)= -

The substraction does not occur

What I would like as a result is

$(vMaxDate)=42213

$(vMinDate)=42033

What is the right way to do this?

(NB : PivotDate is a date#)

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Yes, works like this.

View solution in original post

7 Replies
maxgro
MVP
MVP

use date function to format

1.png

and this for a detailed explanation

Get the Dates Right

Anonymous
Not applicable
Author

Thanks, I know how to transform 42033 into 29/01/2015, but my questions are:

1)Why, vMaxDate being a date (and not a date#), does substracting a number of days transform it into a date#? The answer can be, "it's the way it is" but then it seems to me the whole point of having date and date# falls apart if one can change into the other like this.

2) And also, why if I work with vMaxDate = date#(max({1} PivotDate ), 'DD/MM/YYYY'), does substracting a value not work? date# being a number, it would be logical that it worked.

sunny_talwar

I think you are thinking about this incorrectly. Date# is not a number format, it is a function which is used to tell QlikView that your data is in this format. For instance you have a Excel file where date is like this 2015-01-31 and QlikView reads it as a text field. At that time you can use Date#() function to help QV understand that this is actually a date like this Date#('2015-01-31', 'YYYY-MM-DD'). and now if you wish to take this an convert it into a format of your liking, you can rap it around with Date() function with the format you want. Something like this: Date(Date#('2015-01-31', 'YYYY-MM-DD'), 'MM/DD/YYYY').

First Try:

SET vMaxDate = date(max({1} PivotDate ), 'DD/MM/YYYY');

SET vMinDate = $(vMaxDate) - 180;

Result :

$(vMaxDate)=28/07/2015

$(vMinDate)=42033

You see Date format for vMaxDate because it  was rapped around with a date() function, but vMinDate isn't. If you did it this way:

First try :

SET vMaxDate = max({1} PivotDate), ;

SET vMinDate = $(vMaxDate) - 180;

You would see both of them as numbers.

Second Try:

SET vMaxDate = date#(max({1} PivotDate ), 'DD/MM/YYYY');

SET vMinDate = $(vMaxDate) - 180;

Result :

$(vMaxDate)=42213

$(vMinDate)= -

You are trying to tell QlikView to read a number as date in the format DD/MM/YYYY which it isn't able to do and making it a text formatted variable and when you subtract something from a text you get null.

I hope all of the above would make sense.

Best,

Sunny

Kushal_Chawda

Try

SET vMaxDate = max({1} PivotDate );

SET vMinDate = $(vMaxDate) - 180;

maxgro
MVP
MVP

from what I understand if you use some math ( subtracting -180) or the source field is a number Qlik will return the number as in vMinDate, vMaxDate

in your case (the source is a 42xxx) I think you can use

SET vMaxDate = max({1} PivotDate );          // number

SET vMinDate = $(vMaxDate) - 180;            // number

1.png

2)

Second try:

SET vMaxDate = date#(max({1} PivotDate ), 'DD/MM/YYYY');

SET vMinDate = $(vMaxDate) - 180;

I think this is an error because

SET vMaxDate = date#(max({1} PivotDate), 'DD/MM/YYYY');

if PivotDate is a number, max is a number, and you can't interpret as DD/MM/YYYY

so you get a '-' in vMinDate

Anonymous
Not applicable
Author

Yes, works like this.

Anonymous
Not applicable
Author

Yes works.