Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a issue.
I have a graphic who shows values of the last 30 days, but my last day always have a value problem.
I need to set a range date from 29/09/2022 to 10/10/2022. How using set analysis I can do that?
I using this for the dimension of the graph
=aggr(if(rank([Data Contábil],-[Data Contábil])<=31,[Data Contábil]),[Data Contábil])
Thank You.
hi @Heurasein
not sure if this is the cause, maybe you could try changing the following
[Data Contábil]={">=$(=date(today()-30,'DD/MM/YYYY'))<=$(=date(today(),'DD/MM/YYYY'))"}
now, be aware that my formula takes 30 days back from today,
if you need 30 days back from max [Data Contábil] you will need to change
[Data Contábil]={">=$(=date(today()-30))<=$(=today())"}
by
[Data Contábil]={">=$(=date(max([Data Contábil])-30,'DD/MM/YYYY'))<=$(=date(max([Data Contábil]),'DD/MM/YYYY'))"}
do next exercise
make a table with [Data Contábil] as dimension
use my today or max date setanalysis on each expression
measure1: Sum({1< ......,TAG_FONTE={'SLD_HA','RES_HA'}>}VALOR)
measure2: Sum({1< ........,TAG_FONTE={'SDA','RES_DD'}>}VALOR)
measure3: Sum({1< ........,TAG_FONTE={'SLD_HA','RES_HA'}>}VALOR)
measure4: (column(1)-column(2))/column(3)
that way you can see waht the formula is doing in each day
hope this works.
best,
Hi @Heurasein
I don’t understand why you are using rank
What is usually done is to use only the date field as dimension
and you set the range in the measure
sum({< date_field={">=$(=date(today()-30))<=$(=today())"}>} field)
best,
My field in measure is a formule, is this the problem,
I use this for the field
FABS((Sum({1}{<TAG_FONTE={'SLD_HA','RES_HA'}>}VALOR)-Sum({1}{<TAG_FONTE={'SDA','RES_DD'}>}VALOR))/Sum({1}{<TAG_FONTE={'SLD_HA','RES_HA'}>}VALOR))
Is why I using rank in dimension
I would try two things
first, change Sum({1}{<TAG_FONTE to Sum({1<TAG_FONTE
second, add the date to your measure
FABS((Sum({1< date_field={">=$(=date(today()-30))<=$(=today())"},TAG_FONTE={'SLD_HA','RES_HA'}>}VALOR)-
Sum({1< date_field={">=$(=date(today()-30))<=$(=today())"},TAG_FONTE={'SDA','RES_DD'}>}VALOR))/
Sum({1< date_field={">=$(=date(today()-30))<=$(=today())"},TAG_FONTE={'SLD_HA','RES_HA'}>}VALOR))
and for your dimension try to only use the date field
best,
Hi, thanks in advance for the help. But the chart still hasn't taken the last day of data loaded.
I used the expression you give me, but, nothing changes
hi @Heurasein
not sure if this is the cause, maybe you could try changing the following
[Data Contábil]={">=$(=date(today()-30,'DD/MM/YYYY'))<=$(=date(today(),'DD/MM/YYYY'))"}
now, be aware that my formula takes 30 days back from today,
if you need 30 days back from max [Data Contábil] you will need to change
[Data Contábil]={">=$(=date(today()-30))<=$(=today())"}
by
[Data Contábil]={">=$(=date(max([Data Contábil])-30,'DD/MM/YYYY'))<=$(=date(max([Data Contábil]),'DD/MM/YYYY'))"}
do next exercise
make a table with [Data Contábil] as dimension
use my today or max date setanalysis on each expression
measure1: Sum({1< ......,TAG_FONTE={'SLD_HA','RES_HA'}>}VALOR)
measure2: Sum({1< ........,TAG_FONTE={'SDA','RES_DD'}>}VALOR)
measure3: Sum({1< ........,TAG_FONTE={'SLD_HA','RES_HA'}>}VALOR)
measure4: (column(1)-column(2))/column(3)
that way you can see waht the formula is doing in each day
hope this works.
best,
Thank you very much! Resolved my issue
best,
Great @Heurasein
thanks for the confirmation !!!
the next step would be to save min and max date as variables so you can reuse and your app will be easier to maintain
vMinDate
=date(max([Data Contábil])-30,'DD/MM/YYYY')
vMaxDate
=date(max([Data Contábil]),'DD/MM/YYYY')
use the = when creating the variable
and now in your setAnalysis
….[Data Contábil]={">=$(vMinDate)<=$(vMaxDate)"}…
best,