Help with Date Expression

[antoniodneto]

Hi guys!

I have one DATE FIELD as DD/MM/YYYY format.

I'm trying to plot 2 charts:

One with last 2 years, and another with last 13 months.

How Can I use on my SALES value?

Tks

[PauloHSantos]

You can set the day moth year formact using:

Year(Date("Data field",'DD/MM/YYYY')) as Y,
Month(Date("Data field",'DD/MM/YYYY')) as M,
Day(Date("Data field",'DD/MM/YYYY')) as D,

after this your can have the fields to separate what you want

[PauloHSantos]

after 'as' you set the name of dimension so, this is provided as an example

[PauloHSantos]

to set the interval you can just make a set analysis to determinate the interval so filters will not influence your plot

[antoniodneto]

Hi Paulo,

The date field worked. How Can I use the set analysis to show just the last 2 years and another with last 12 months?

Tks!

[PauloHSantos]

aggr(ONLY({<YEAR={'2017'}>} Field ),Field)

[vishsaggi]

May be you want to create an Year field in your data table like

Year(DateFIeld) as Year
then try these two exprs for your last 2years and last 13 months.

= Sum({< Year = {">=$(=Max(Year)-2)<=$(=max(Year)))"}>} SalesField)

= Sum({< DateField = {">=$(=Num(Floor(AddMonths(DateField, -13)))<=$(=Num(Floor(Max(DateField))))"} >} SalesField)

[antoniodneto]

Hi Vishsaggi.

I'm trying to solve the last 13 months, but I couldn't get it.

My expression

SUM({< DATA = {">=$(=Num(Floor(AddMonths(DATA, -13)))<=$(=Num(Floor(Max(DATA))))"} >}CART_ATRASO_31a60)

[vishsaggi · Accepted solution]

Sorry your field DATA should be in number format. Like create a field in your data table like

LOAD field1, field2, DATA,
Num(Floor(Date#(DATA, 'DD/MM/YYYY'))) AS DATAID
FROM tablename;

Add this DATAID in listbox and see if you find any numbers. Then use this DATAID in your sum() expression like
SUM({< DATAID = {">=$(=Num(Floor(AddMonths(DATA, -13)))<=$(=Num(Floor(Max(DATA))))"} >}CART_ATRASO_31a60)