calculated issue

[soniasweety]

Hi All,

@Kushal_Chawda @sunny_talwar  @Vegar  Taoufiq_ZARRA

i want to create calcuated dimension to show max 7days data in bar chart but im facing issue.

Mydate (MM/DD/YYYY)  format 

08/20/2020

08/21/2020

im using this expression but showing invalid visualization  tried below two ways

variable   :    =Max(Num(mydate))-7

 =If(mydate>$(maxdate),Date(mydate,'MM/DD/YYYY'),NULL())        (Not working)

=if(mydate >= max(mydate)-7 and mydate < max(mydate), mydate)    (Not working

[Kushal_Chawda]

@soniasweety assuming your dates are in proper date format try below

=aggr(only({<Mydate={">=$(=date(max(Mydate)-7,'MM/DD/YYYY'))<=$(=date(max(Mydate),'MM/DD/YYYY'))"}>}Mydate),Mydate)

[sunny_talwar · Accepted solution]

You can also try this

=If(mydate >= Max(TOTAL mydate) - 7 and mydate < Max(TOTAL mydate), mydate) 

[soniasweety]

@sunny_talwar  , @Kushal_Chawda  thnks . but both solutions is not working 😞

my date in script  is  like this.

Date(subfield(mydate,',',1)) as mydate      

any issue with this?

[sunny_talwar]

How does mydate field look like to begin with? What do you get when you use our expressions? Error message? in correct output? what exactly do you mean it is not working?

[Kushal_Chawda · Accepted solution]

@soniasweety  looks like it is already formatted as date so can you try below. If still not working send a screenshot of your date field in filter

=aggr(only({<Mydate={">=$(=date(max(Mydate)-7))<=$(=date(max(Mydate)))"}>}Mydate),Mydate)

[soniasweety]

@sunny_talwar data count showing  more records ..

mydate original format looks like below

2020-08-20  00:0:24:26.000000 

[soniasweety]

hi @Kushal_Chawda ,

mydate original format looks like below

2020-08-20  00:0:24:26.000000 

user want  mm/dd/yyyy format

[Kushal_Chawda]

@soniasweety  first convert it to date format  like below

LOAD *,

           date( floor(Mydate)) as Mydate

FROM table;

Now you can try the expression which I had suggested earlier

[sunny_talwar]

@soniasweety  where is the comma used in the field? Why are you using comma (',') as a delimiter for SubField?