topic Re: Rolling back 12 months in QlikView

Rolling back 12 months

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I am trying to write an expression in order to present aggregated data for 12 months back from my current (max reporting month) The format that is used in the application is 201604 (for April for example). So if I try 201604 - 12 I get 201592 instead of 201505. Do you have any idea what is the correct way to create this expression so that it calculates correctly?

Thanks,

Mihael

Re: Rolling back 12 months

andrespa — Wed, 22 Jun 2016 16:14:19 GMT

Hi Mihael, try like this:

yourDate - 100

That should work for you.

Best regards,

Andrés

Re: Rolling back 12 months

Gysbert_Wassenaar — Wed, 22 Jun 2016 16:15:34 GMT

Turn those numbers into real dates in the script:

LOAD

...fields...

Date(Date#( [reporting month], 'YYYYMM'),'YYYYMM') as [reporting month]

...more fields....

FROM

...some source...

;

Then you can treat that field [reporting month] as a date:

sum({<[reporting month]={'>$(=Date(AddMonths(max([reporting month]),-12),'YYYYMM'))<=(=Date(max([reporting month]),'YYYYMM'))'}>}Amount)

You can make it even easier if you create a numeric field:

Year(Date#( [reporting month], 'YYYYMM'))*12+Month(Date#( [reporting month], 'YYYYMM')) as monthserial

The set analysis expression would then be:

sum({<monthserial={'>$(=max(monthserial)-12)<=$(=max(monthserial))'}>}Amount)

Re: Rolling back 12 months

Thu, 23 Jun 2016 08:49:28 GMT

The following might help and extend your applications capability:

Subroutine to Create Data Model for From/To Date Selection

Good Luck!