Custom Month-to-date Filter- VERY IMPORTANT

[mallu2u]

Folks/Gurus,
I would like to develop a custom month-to-date SQL with the following logic:
1- If its regular day of the month, I need a month-to-date (1st of the month to yesterday's) data, eg if its july 26th, data should be from july 1-july 25th.
2- if its 1st of the month, I need last month's month-to-date data. if its july 1, data should be from june 1-june 30th.

If this possible in one statement?

Thanks for your time and help in advance,
Sam

 

[User14]

Dear mallu2u

Yes you can get the month-to-date figures. it is very much possible. Please refer to the VMALL model that comes with every MSTR software. In that model they have shown how to create Month-to-date metrics. If you still can't get it, revert back to me.

Thanks.

User14

 

[mallu2u]

Hi,

Part 1 is easy if doe itself but I would like to combine part 1 and part2 into one filter? do u know how?

 

[User14]

Hi,

For the second part you have create a transformational metrics. For that you need to modify your look up table. How to create transformational metric is shown the VMALL project itself. Have a look at and also their data in the warehouse for the transformational metric and month-to-date. We will get some idea. If any more doubt revert back to me.

User14.

 

[fukudahs]

Dear mallu2u,
Instead of two transformation object, you could create one single transformation table like this:
Date MTD
09042002 09032002
09042002 09022002
09042002 09012002
09032002 09022002
09032002 09012002
09022002 09012002
... ...

This table would satisty the two requirements (1- If its regular day of the month, I need a month-to-date (1st of the month to yesterday's) data, eg if its july 26th, data should be from july 1-july 25th.
2- if its 1st of the month, I need last month's month-to-date data. if its july 1, data should be from june 1-june 30th).

Sincerely,
Humberto Seiji Fukuda
cogubr@hotmail.com