BookmarkSubscribeRSS Feed
mjaitly
Fluorite | Level 6

Hi

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.

Screen.png

Thanks

Manish

4 REPLIES 4
PeterWijers
Lapis Lazuli | Level 10

Hi Manish.

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.

greetings Peter

mjaitly
Fluorite | Level 6

Peter

Thanks for your reply. It will be great if you show me a sample formula.

Thanks

Manish

PeterWijers
Lapis Lazuli | Level 10

Manish,

At first it should be a Calculated item and not an aggregated measure.  (Was late yesterday...haha)

First the formula:

Formula1.png

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.

Formula2.png

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

mjaitly
Fluorite | Level 6

Thanks a ton Peter!

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 4 replies
  • 1521 views
  • 3 likes
  • 2 in conversation