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: 
antoniodneto
Creator II
Creator II

Help with Date Value Expression

Hi Guys,

I'm confused about an expression

I have my date field with format YYYYMM.

I want to returne the value os MAX DATE in a column and MAX DATE - 3.

When I use am KPI to return the dates it works =MAX(DATE) = 201901 and =MAX(DATE,3) = 201811

But when I tried to use on my table to show the measures I can't find the value for -3 months.

To MAX DATE I'm using =sum({<SAFRA_ADESAO={"=$(=max(SAFRA_ADESAO))"}>}VL_OVER30_M3)/1000000

To -3 months =sum({<SAFRA_ADESAO={"=$(=max(SAFRA_ADESAO,3))"}>}VL_OVER30_M3)/1000000

But is not working. Am I forgetting something?

TKS ALL!!!

1 Solution

Accepted Solutions
Channa
Specialist III
Specialist III

why you doing -3 to get max date

 

=sum( {< SAFRA_ADESAO={"<=$(=Date(Max(SAFRA_ADESAO),'YYYYMM')) >=$(=Date(AddMonths(Max(SAFRA_ADESAO),-3),'YYYYMM'))" }>} VL_OVER30_M3)

Channa

View solution in original post

4 Replies
sunny_talwar

May be this

 =Sum({<SAFRA_ADESAO = {"$(=Max(SAFRA_ADESAO,3))"}>} VL_OVER30_M3)/1000000

or this

 =Sum({<SAFRA_ADESAO = {"$(=Date(Max(SAFRA_ADESAO,3), 'YYYYMM'))"}>} VL_OVER30_M3)/1000000
antoniodneto
Creator II
Creator II
Author

Sunny,

I got using this expression =sum( {< SAFRA_ADESAO={">=$(=Date(AddMonths(Max(SAFRA_ADESAO),-3),'YYYYMM')) <=$(=Date(AddMonths(Max(SAFRA_ADESAO),-3),'YYYYMM'))" }>} VL_OVER30_M3)

Do you think that is an easier way? Any another idea?

Channa
Specialist III
Specialist III

why you doing -3 to get max date

 

=sum( {< SAFRA_ADESAO={"<=$(=Date(Max(SAFRA_ADESAO),'YYYYMM')) >=$(=Date(AddMonths(Max(SAFRA_ADESAO),-3),'YYYYMM'))" }>} VL_OVER30_M3)

Channa
antoniodneto
Creator II
Creator II
Author

It worked as well. But your expression looks better. Tks again Channa and Sunny!!!!