max date and max -1

[yogitamehta10]

HI All,

I'm getting date added in my sheet ramdomly.

i want to calculate sum of  sales where date  is maximum date and  1 less from maximum ,

so latest date and last lastest date.

how should i define it.

22/04/2016

28/04/2016

01/05/2020

[sunny_talwar]

I am not seeing anything attached?

[yogitamehta10]

Please check

[sunny_talwar]

In your attached sample, I see three dates

22/04/2016

28/04/2016

19/05/2016

If Max(Date) = 19/05/2016, then Max(Date) - 1 = 18/05/2016. Since the date is non-existent, you won't see anything

Do you want 1 week ago?

Max(Date) - 7? But for the current sample that won't work because there is no 12/05/2016 in your sample.

or may be you want Max(Date, 2)? This will give you 28/04/2016. It all depends on what you want and what you have. If you can let me know what you want, I can give you a final expression

[yogitamehta10]

Actullly it would be a randon date  it could be a week before or 5 days before , so i want my chart to pick  current latest date  as current week and  2nd latest date as last week

[sunny_talwar]

May be this:

Sum({<date_field={"$(='>=' & Date(Max({1}date_field)-7,'DD-MM-YYYY') & '<' & Date(Max({1}date_field)-0,'DD-MM-YYYY'))"}>}sales)

For the sample above, this will pick all the days which are greater than or equal to 12/05/2016 and less than 19/05/2016. Is there a possibility that you might get multiple dates within that range? or will you get only one date a week?

Alternatively, you can also try this:

Sum({<date_field={"$(=Date(Max({1}date_field, 2),'DD-MM-YYYY'))"}>}sales)

[yogitamehta10]

second one seems to work fine .. Thanks a lot, i will test this by adding more data and let you know

[sunny_talwar]

Yup, keep us posted

[yogitamehta10]

one more thing .. if i change my requiremnt to  current month and last month

so that means  19th of today data will be current month and 19th of last month data would be last month

how i will implemnt this in same thing.

[sunny_talwar]

A single date or the whole month? or month to date?

Also, please try to refrain yourself from posting new requirement in an old thread. Makes it difficult for people looking for similar requirements in the future. Opening a new thread will not just benefit you (because you will get all the new audience) + it will help this community.

[yogitamehta10]

Single date