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

[#1]

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

[sunny_talwar · Accepted solution]

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]

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]

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

[Clever_Anjos]

Just figuring out, it could be any format

[sunny_talwar]

Makes sense

[#9]

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

[#10]

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