Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

antoniodneto
Contributor

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
Valued Contributor III

Re: Help with Date Value Expression

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
4 Replies

Re: Help with Date Value Expression

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
Contributor

Re: Help with Date Value Expression

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
Valued Contributor III

Re: Help with Date Value Expression

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
Contributor

Re: Help with Date Value Expression

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