Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rolling back 12 months

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

3 Replies
andrespa
Specialist
Specialist

Hi Mihael, try like this:

yourDate - 100

That should work for you.

Best regards,

Andrés

Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
Not applicable
Author

The following might help and extend your applications capability:

Subroutine to Create Data Model for From/To Date Selection

Good Luck!