Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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)
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
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?
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)
It worked as well. But your expression looks better. Tks again Channa and Sunny!!!!