Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

HELP! Can't subtract days from max(date)

Hi I would really appreciate if someone could help me with a problem I've been stuck on for a while using Qlik Sense:

First i created a variable vLAST_DATE = MAX(DATE);

I have not been able to use it. This worked instead: =Sum({$<DATE={'=MAX(DATE)'}>}SALES) but I don't know how to put my variable in there and I tried many different combinations.

Now the problem is I would like to get a hold of MAX(DATE) - 7 days, just writing MAX(DATE) - 7 does nothing I still get the same MAX(DATE) results.

Ideally I would like to use my variable instead, that way I can create vSECOND_DATE = MAX(DATE) - 7; but any answer that results in giving the right visualization in my bar chart will do. Thanks!

/Sahir

1 Solution

Accepted Solutions
sunny_talwar

to answer your first question, try this:

=Sum({$<DATE={"$(vLAST_DATE)"}>}SALES)

to answer your second questions, try this:

=Sum({$<DATE={"$(=Date(Max(DATE) - 7))"}>}SALES)

View solution in original post

10 Replies
sunny_talwar

to answer your first question, try this:

=Sum({$<DATE={"$(vLAST_DATE)"}>}SALES)

to answer your second questions, try this:

=Sum({$<DATE={"$(=Date(Max(DATE) - 7))"}>}SALES)

sunny_talwar

Or you can create this variable:

vSECOND_DATE = Date(Max(DATE)-7)

=Sum({$<DATE={"$(vSECOND_DATE)"}>}SALES)

Clever_Anjos
Employee
Employee

Probably you are having problems using formatted dates

Try using

Sum({$<DATE={"$(=DATE(MAX(DATE),'MM/DD/YYYY')"}>}SALES)

Sum({$<DATE={"$(=DATE(MAX(DATE)-7,'MM/DD/YYYY')"}>}SALES)

sunny_talwar

Although I suspect the same, but how did you come to know that Clever‌‌? I don't see any date here, how did you figured that his date is in this format? MM/DD/YYYY?

Curious Sunny

Anonymous
Not applicable
Author

change the variable to date format and then use that in the set analysis

Clever_Anjos
Employee
Employee

Just figuring out, it could be any format

sunny_talwar

Makes sense

Not applicable
Author

Thanks guys! Just got back to work. sunindia‌ Im goint to try your suggestions. cleveranjos‌ I use YYYY-MM-DD so I will try to put that in the expression if the first suggestion doesn't work. @Vinay Thanks will do, I just got in to work after the weekend so sorry for late reply!

Cheers!

Sahir

Not applicable
Author

Thanks for the help guys! sunindia‌ your code worked perfectly so I stopped there