topic Rollback to 30days in QlikView

Rollback to 30days

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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...

Re: Rollback to 30days

MayilVahanan — Wed, 15 Jun 2016 06:13:36 GMT

Try like this

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

Re: Rollback to 30days

nareshthavidishetty — Wed, 15 Jun 2016 06:35:46 GMT

Hi,

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

Thanks..

Re: Rollback to 30days

nareshthavidishetty — Wed, 15 Jun 2016 06:41:19 GMT

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..

Re: Rollback to 30days

MayilVahanan — Wed, 15 Jun 2016 07:20:01 GMT

Try like this

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

Re: Rollback to 30days

pho3nix90 — Wed, 15 Jun 2016 08:15:27 GMT

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.

Re: Rollback to 30days

pho3nix90 — Wed, 15 Jun 2016 08:18:48 GMT

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.