Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Mihael, try like this:
yourDate - 100
That should work for you.
Best regards,
Andrés
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)
The following might help and extend your applications capability:
Subroutine to Create Data Model for From/To Date Selection
Good Luck!