need a macro

[Former Member]

Helo Gurus

please let me know how to write a macro for the below

there is a report where i have MTD,QTD,YTD,FY tabs for all the 7 products with Actual,plan and Budget versions

all the tabs must expand based onthe selection of the month in the MTD tab which I have accomplished

MTD is fine, when in QTD i want to get the month data for all the three versions ( i know i can get this by hidden sheets with the expansion on all the three versions but this is going to be 7 * 3 which is 21 with all the 12 months for each i.e this is going to be a performance problem and an issue with excel being slow, since already there are 4 Tabs)

the issue with QTD is , since our year starts in april and the quarter is aril may and june , and if the user selects may in MTD the report must drive may to QTD and YTD respectively

when may in QTD the accumulation must happen for april and may

if YTD also goes the same

thanks

[Former Member · Accepted solution]

Hi,

I didnt understand the need for a macro, here.

What happens when you select the month MAY manually in the MTD tab? Do you get the correct figure?

You just need to refer to the time member from the first tab. This reference should be maintained in all the other tabs in the control panel. The system will automatically fetch the appropriate values.

I am assuming that you have maintained these measures in your system.

Hope this helps.

[Former Member · Accepted solution]

Yes

Nilanjan

The system picks all the month based on the selection

that is fine

i was trying to explain in regards to the QTD and YTD

1. Since QTD should be for example

if month May is selected thn for the first quarter i need the total of april and may as may value and not just may

2. this can be achived by having all the months as hidden sheet but i need plan, budget and actuals so there would be 3(versions) * 16(apr-mar, q1,q2,q3,q4) * 7(products) and then apply excel functions

to avoid the above calculation if there could be any better way to handle this

thanks

[Former Member · Accepted solution]

Hi,

I agree, there is no macro needed.

When you select the QTD measure, when pointed to MAY, you will get the sum of the April & May data.

MTD, or Periodic measure selection will give you the MAY data. And, YTD will give you the sum of the Aptil + May data.

The point here is, that you do not need to pull in all 12 months of data into your spreadsheet. That beign said, if you require reports for each month to be run over and over, then yes, it will be better to pull in all the data at once and then use formulas to pull of the columns that you need.

If you will be doing this, then look into the Excel formula "Indirect". That allows you to point to a particular column to pull in the data that is selected.

[Former Member · Accepted solution]

Hi

Jeff

As i am new to BPC i would liket o understand more clearly

does that mean that YTD and QTD have no difference if it is May DATA ?

thanks

[Former Member · Accepted solution]

Hi,

If your year starts from APR, then QTD and YTD of APR, MAY and JUN are going to be the same. This is because the quarter and the year both are starting from the same period.

Hope this helps.