Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Help Needed on MTD Aggregate Measure

Reply
Occasional Contributor
Posts: 19

Help Needed on MTD Aggregate Measure

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

Regular Contributor
Posts: 172

Re: Help Needed on MTD Aggregate Measure

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

Occasional Contributor
Posts: 19

Re: Help Needed on MTD Aggregate Measure

Peter

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

Thanks

Manish

Regular Contributor
Posts: 172

Re: Help Needed on MTD Aggregate Measure

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

Occasional Contributor
Posts: 19

Re: Help Needed on MTD Aggregate Measure

Thanks a ton Peter!

Post a Question
Discussion Stats
  • 4 replies
  • 522 views
  • 3 likes
  • 2 in conversation