locked
current month with mdx: problem RRS feed

  • Question

  • hi there

    I have a current month script that actually works pretty good. The only problem is that I never get the numbers for the last day of the month, since the script seems to have troubles because it would be generated on the 1st of the next month.

    Can you maybe help me? I can't figure out what to change to make sure I get the numbers for the last day of the month on the first day of the following month.

    ="[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW()))),2) + "]"

    The report is generated every morning around 7 and sent to several email adresses automatically.

    Thanks

    Sabrina


    Thursday, July 19, 2012 11:30 AM

Answers

  • Hey Sabrina,

    There's actually nothing wrong with your script. Let me try and explain what I feel mostly is the problem:

    So you have a cube with a Dimension named "InvoiceDate". This dimension then has an attribute named "Month". The members of the "Month" attribute have their member unique names as -

    [Invoice Date].[Month].&[2012-07] (for July)

    [Invoice Date].[Month].&[2012-08] (for August) and so on....

    Now, the reason you don't see the data for the previous day of the month is because as the day has changed to the 1st of the next month, your context in the cube has changed. So it's correct for the script to not get data for the previous day.

    Here's probably what you can try:

    =IIf (
    	DatePart(DateInterval.Day, Now()) = 1,
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW())) - 1),2) + "]",
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW()))),2) + "]"
    )

    The above script will now check if the current date is the 1st of any month and if yes, will set itself to the previous month.

    Let me know if you need more info.

    HTH.

    Cheers,

    IceQB


    Please mark correct answers :)

    Thursday, July 19, 2012 12:06 PM
  • Well no... The way the expression in my previous mail would work would be that it would get you the numbers for the whole of the previous month only. If you want data for the whole of the year until the previous day, you would have to rewrite the expression a little differently as:

    =Switch (
    	DatePart(DateInterval.Day, Now()) = 1 And DatePart(DateInterval.Month, Now()) = 1,
    	"{[InvoiceDate].[Month].&[" + CStr(Year(NOW()) - 1) + "-01" + "]" 
    	+ ":" +
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW()) - 1) + "-12" + "]}",
    
    	DatePart(DateInterval.Day, Now()) = 1 And DatePart(DateInterval.Month, Now()) > 1,
    	"{[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-01" + "]"
    	+ ":" +
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW())) - 1),2) + "]}",
    		
    	True,
    	"{[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-01" + "]"
    	+ ":" +
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW()))),2) + "]}"
    )

    What the above expression will do is to return an MDX set of months prior to the current month. So, for example:

    1. When the current date is 2013-01-01, the expression will return 2012-01 through 2012-12 (sum of the whole of previous year)

    2. When the current date is 2012-07-01, the expression will return 2012-01 through 2012-06

    3. When the current date is 2012-07-19, the expression will return 2012-01 through 2012-07

    Let me know if you need more info.

    HTH.

    Cheers,

    IceQB


    Please mark correct answers :)



    Thursday, July 19, 2012 2:44 PM

All replies

  • The expression I always use to calculate the last day of the previous month is:

    =DateAdd("d", -1, DateSerial(Year(Today()), Month(Today()), 1))

    Thursday, July 19, 2012 11:33 AM
  • thanks for your answer.

    but I actually want to keep the existing script and just add information.
    there must be a reason why the person who did my job before me used that script.

    Thursday, July 19, 2012 11:51 AM
  • Hey Sabrina,

    There's actually nothing wrong with your script. Let me try and explain what I feel mostly is the problem:

    So you have a cube with a Dimension named "InvoiceDate". This dimension then has an attribute named "Month". The members of the "Month" attribute have their member unique names as -

    [Invoice Date].[Month].&[2012-07] (for July)

    [Invoice Date].[Month].&[2012-08] (for August) and so on....

    Now, the reason you don't see the data for the previous day of the month is because as the day has changed to the 1st of the next month, your context in the cube has changed. So it's correct for the script to not get data for the previous day.

    Here's probably what you can try:

    =IIf (
    	DatePart(DateInterval.Day, Now()) = 1,
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW())) - 1),2) + "]",
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW()))),2) + "]"
    )

    The above script will now check if the current date is the 1st of any month and if yes, will set itself to the previous month.

    Let me know if you need more info.

    HTH.

    Cheers,

    IceQB


    Please mark correct answers :)

    Thursday, July 19, 2012 12:06 PM
  • Hey IceQB

    Thanks alot.

    I have to do the same report for the current year.

    Could you maybe help me with that as well?

    Same thing checking on today calculation till the day before - make sure the 31st of December will not be missed.

    Cheers
    Sabrina

    Thursday, July 19, 2012 1:44 PM
  • Hi Sabrina,

    To handle the current year, or 1st January, we can extend the same expression we wrote previously as:

    =Switch (
    	DatePart(DateInterval.Day, Now()) = 1 And DatePart(DateInterval.Month, Now()) > 1,
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW())) - 1),2) + "]",
    	
    	DatePart(DateInterval.Day, Now()) = 1 And DatePart(DateInterval.Month, Now()) = 1,
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW()) - 1) + "-12" + "]",
    	
    	True,
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW()))),2) + "]"
    )

    So, following will be the cases:.

    1. When the current date is 2013-01-01, the expression will return 2012-12

    2. When the current date is 2012-07-01, the expression will return 2012-06

    3. When the current date is 2012-07-19, the expression will return 2012-07

    Let me know if you need more info.

    HTH.

    Cheers,

    IceQB


    Please mark correct answers :)

    Thursday, July 19, 2012 2:06 PM
  • Thanks alot.

    Am I right that in each case it will bring me the numbers of the whole ongoing year until the previous day?

    Thursday, July 19, 2012 2:13 PM
  • Well no... The way the expression in my previous mail would work would be that it would get you the numbers for the whole of the previous month only. If you want data for the whole of the year until the previous day, you would have to rewrite the expression a little differently as:

    =Switch (
    	DatePart(DateInterval.Day, Now()) = 1 And DatePart(DateInterval.Month, Now()) = 1,
    	"{[InvoiceDate].[Month].&[" + CStr(Year(NOW()) - 1) + "-01" + "]" 
    	+ ":" +
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW()) - 1) + "-12" + "]}",
    
    	DatePart(DateInterval.Day, Now()) = 1 And DatePart(DateInterval.Month, Now()) > 1,
    	"{[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-01" + "]"
    	+ ":" +
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW())) - 1),2) + "]}",
    		
    	True,
    	"{[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-01" + "]"
    	+ ":" +
    	"[InvoiceDate].[Month].&[" + CStr(Year(NOW())) + "-" + Right("0" + CStr(Month(DateAdd("d",0,NOW()))),2) + "]}"
    )

    What the above expression will do is to return an MDX set of months prior to the current month. So, for example:

    1. When the current date is 2013-01-01, the expression will return 2012-01 through 2012-12 (sum of the whole of previous year)

    2. When the current date is 2012-07-01, the expression will return 2012-01 through 2012-06

    3. When the current date is 2012-07-19, the expression will return 2012-01 through 2012-07

    Let me know if you need more info.

    HTH.

    Cheers,

    IceQB


    Please mark correct answers :)



    Thursday, July 19, 2012 2:44 PM