I need some urgent help on an aggregate measure. I have an MTD report that shows data like 1st to 22nd April, 1st to 22nd March, 1st to 22nd Feb, 1st to 22nd Jan and likewise. It is an MTD like-for-like period monthly comparison report. In my company we upload data daily for the previous day. Please see the attached screenshot. In this aggregated measure, I want to know how instead of manually changing the date every day, I can set the date formula so that it automatically considers yesterday's date.
PS - There is another option in the field and that is TODAY however if I choose this option, figures will not appear correctly as for April (assuming I run the report today 23rd April) it will show data from 1st to 22nd April (as mentioned above we have data till yesterday 22nd april in our servers) but for Mar and preceding months, it will show from 1st to 23rd instead of 1st to 22nd.
Please...any help will be appreciated.
i Would suggest a different approach.
create a new aggregated calculated field where you filter on the day number of the month less than the day number of today.
While using the months in the report, it should show the results of each month less than the daynumber of today.
if you might need more help, i could set-up a formula sample for you.
At first it should be a Calculated item and not an aggregated measure. (Was late yesterday...haha)
First the formula:
So if the "day of the month" of the transaction Date is less than the "day of the month" of today (the 24th "now") then the weight is transfered to the variable kg_form, if not the kg_form gets 0
Here the result.
As you can see in this cross table, all data reporting stops at the 24th day of the month.
This should work for you in this case.
Ones you are used to these kind of calculated items, there are a lot of possibilities.
Greetings and good luck. Peter
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.