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
Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.
Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.
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.