Rollback to 30days

[nareshthavidishetty]

Hi,

We are using the dates in the report from monthstart to till date  and we need to get the date range from exactly last 30days. i.e. 14/05/2016 to 14/06/2016 dynamically.

Thanks...

[MayilVahanan]

Hi

Try like this

= Sum({<Year=, Month=, Quarter=, Week=, Date= {">=$(=Date(AddMonths(max(Date),-1),'DD-MM-YYYY')) <=$(=Date(max(Date),'DD-MM-YYYY'))"}>}YourFieldExpressin)

[nareshthavidishetty]

Hi,

We were using date as dimension..and we need to sort the data at dimension level.

Thanks..

[nareshthavidishetty]

Hi,

Below is the logic used in dimension to get MTD.

=if(App_Date>=date(MonthStart(today()-1),'M/D/YYYY') and App_Date<date(today(),'M/D/YYYY'),App_Date)

It gives data from 6/1/2016 to 6/14/2016..required date range  6/14/2016 rollback to 5/15/2016 means 30 days rollback (logic is to be dynamic)

Thanks..

[MayilVahanan · Accepted solution]

Hi

Try like this

=if(App_Date>=date(AddMonths(today(),-1),'M/D/YYYY') and App_Date<date(today(),'M/D/YYYY'),App_Date)

[pho3nix90]

If the date dimension is as granular to the day or month, then this will have to be done in script.

If you can provide a sample qvw or reduced on I will assist you. 

[pho3nix90]

I am not sure if this will work without knowing your exact data schema and what other dimensions you are using and expressions, however the code you posted below

=if(App_Date>=date(MonthStart(today()-1),'M/D/YYYY') and App_Date<date(today(),'M/D/YYYY'),App_Date)

should be

=if(App_Date>=date(today()-30,'M/D/YYYY') and App_Date<date(today(),'M/D/YYYY'),App_Date)

The reason being the following, if we assume the date is "14-06-2016" it will minus 1 day which will be "13-06-2016", then you are using a function to return monthstart which will be "01-06-2016", however, you want to do exact 30 date span, so substracting only 30 without month start will give you the results you want, if not you will have to post an example.