Get the data of a year prior to the selected date

[jvafb]

Hello

I have created a table where I want to show the sum of the RATIO measure of the selected date (field DATE) and the data of one year before to such date. For the selected date I use:

Sum(RATIO)

It works fine, but for the previous year I use:

Sum({<DATE= {"$(=Date(AddMonths(Max(DATE), -12)))"}>} RATIO)

But it shows zero (0), could you help me with that formula?

Thank you

[sidhiq91]

@jvafb  The expression is perfect, even I tried the same with the below expression and it works perfectly fine.

Sum({<OrderDate={"$(=Addmonths(Max(OrderDate),-12))"}>}LineSalesAmount)

Just check if there is data for that particular date when it goes to the previous year.

If you still have any confusion, please attach the sample data and I will look into it.

[vinieme12]

Use AddYears() instead

Sum({<DATE= {“$(=Date(AddYears(Max(Date),-1)))”}>} RATIO)

Or specify the date field format of DATE field 

Sum({<DATE= {“$(=Date(AddYears(Max(Date),-1),’YYYY/MM/DD’))”}>} RATIO)

replace YYYY/MM/DD With the date format as in DATE field

[jvafb]

I use a fixed date:

Sum({<DATE={'2022-03-31 00:00:00.000000'}>} RATIO)

And it works, I think it is failing because I am using a Date type instead of a timestamp, how can I transform a date to a timestamp with hours 00:00:00.000000?

[vinieme12]

Would rather Convert your timestamp field into a date field or create a new field with only the date part

Date(Floor(DATE)) as Datefield 

Then just use below

Sum({<Datefield= {“$(=Date(AddYears(Max(Datefield),-1)))”}>} RATIO)

[jvafb]

I load data with a sql query in the editor, where can I convert or create the field?

[vinieme12]

In the Data load Editor add a preceding load as below highlighted

TAbleName:

LOAD

*

,Date(Floor(timestampField)) as Datefield 

;

SQL 

Select field1,field2,timestampField

From db.tablename;