Date in QLIK

[Heurasein]

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.

[RafaelBarrios]

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,

[Heurasein]

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 

[RafaelBarrios]

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,

[Heurasein]

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

[RafaelBarrios · Accepted solution]

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,

[Heurasein]

Thank you very much! Resolved my issue

best, 

[RafaelBarrios]

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,