YTD - Please Help

[Former Member]

Hello BI Gurus,

I'm updating the BI Content Query 0PUR_C01_Q0018 (Variances, goods receipt)where I'm filtering by <b>Material Group down </b> and <b>Calendar Year/Month Across</b> for each vendor and I would like to know how to create a <b>YTD</b> column <i>(last column)</i> that sums up the variances for each vendor for all the months.

Thanx in advance

Best Regards,

Osman

[Former Member]

Hi,

I dont thing it will allow you to change the standard query contnet.But you can copy the query and change it.

Go to the key figurethat you want YTD for ,rt clik to go to Edit option .

Drag 0FISCYEAR with variable 0P_FYEAR and 0FISCPER3(Posting period) with variable 0P_PER3

Create a new selection for YTD for that KF , here again use the same key figure with the same Key figure and drag 0P_FYEAR and then drag the Posting Period 0FISCPER3 ,

Select the options of Value range where you select the start of range as Janaury and end of range as the variable 0P_PER3 varaible.

This should give you YTD for that key figure

[Former Member]

Hi timur,

Thank you for the quick reply to my post. I don't all have the fields that you mentioned. I only have the following fields in my time dimension:

Calendar Year/Month

Calendar Year/Week

Fiscal Year Variant

Fiscal year/period

Any workarounds???

Thanks

Osman

[Former Member]

You may have to create a Customer Exit Variable for calculating YTD given only Fiscal Period.

<b>Customer Exit - > YTD from Fiscal Period</b>

<b>Let ZVCCYTDX be the tech name of Customer Exit Variable & ZVCPAXRG be the user entered variable for Fiscal Year Period.</b>

-

---

*

User Exit: ZVCCYTDX

• Input: ZVCPAXRG

• Output: Jan of Accident Year Range

• Logic Description:

----

---

WHEN 'ZVCCYTDX'.

if i_step = 2.

clear loc_var_range.

loop at i_t_var_range into loc_var_range WHERE vnam = 'ZVCPAXRG'.

• if sy-subrc = 0.

curr_year = loc_var_range-low(4).

concatenate curr_year '001' into jan_accidentyear.

CLEAR L_S_RANGE.

L_S_RANGE-LOW = jan_accidentyear.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'EQ'.

append l_s_range to e_t_range.

exit.

**endif.

endloop.

endif.

Coding is not required if you can enhance the Datatarget with Fiscal Year and Posting Period.

[Former Member]

Hi Roberto,

Thank you for your post. Can you please clarify your last comment.

<i>

Coding is not required if you can enhance the Datatarget with Fiscal Year and Posting Period.</i>

Does this mean that I just need to add in <b>Fiscal Year</b> and <b>Posting Period</b> into the cube and I should be fine???

Thanks a lot

Best Regards,

Osman

[Former Member]

You can just add Fiscal Year and Posting Period to the Cube and map them to Fiscal Period in the Update rules - Automatic Conversion will take place.

However, the tradeoff here would be that the User will have to enter Fiscal Year and Posting Period seperately instead of entering Fiscal Period only.

( 2007 + 001 ) or ( 2007001 )

Btn Variances is a keyfigure or a Selection/RKF ?

[Former Member]

Hi Roberto,

Let me try that real quick.

Variances that I mentioned in my original post is a CKF (<i>Price variance of goods receipt value to be exact</i>)

Thanks

Best Regards,

Osman

[Former Member]

Hi Roberto,

When I go in the update rules I only have the following as my selections and I don't see <i>"Fiscal Year"</i> as you mentioned to try

<i>Document Date

Positng date in the document

Date on which the purchasing document was created

Validity period end

Validity period start</i>

The other fields in the time dimension are all mapped to<i> "Date on which the purchasing document was created"</i> so I tried the same for the two new fields - <b>Posting Period</b> and <b>Fiscal Year</b> but no data came in.

any thoughts???

Best Regards,

Osman

[Former Member]

After adding Fiscal Year and Posting Period - In Update Rules - > Time Ref - > Click on Triangle for Fiscal Year and give Source Char as Fiscal Period.

Do the same for Posting Period.

( As per your above post Fiscal Period is available in the Cube )

[Former Member]

Hi Roberto,

Few clarifications on my side:

We're USING the following TWO standard update rules:

<b>0PUR_C01 2LIS_02_ITM</b> - which has the following choices for the Source Char

<i>0DOC_DATE Document Date

0PSTNG_DATE Posting date in the document

0ENTRY_DATE Date on which the purchasing document was created

0VAL_END Validity period end

0VAL_START Validity Period Start</i>

<b>0PUR_C01 2LIS_02_SCL</b>

<i>0DOC_DATE Document Date

0PSTNG_DATE Posting date in the document

0SCL_DELDAT Planned delivery date of document schedule line

0SCHED_DATE Schedule line date

0ENTRY_DATE Date on which the purchasing document was created

0STAT_DATE Statistics date

0VAL_END Validity period end

0VAL_START Validity Period Start </i>

We are NOT USING the following OLD Update Rules

<b>0PUR_C01 2LIS_02_S012</b> - but this one does have:

<i>0CALDAY Calendar Day

0FISCPER Fiscal year / period </i>

So...any thoughts???

Thanks a lot

Best Regards,

Osman

[Former Member]

Any help on this would be greatly appreciated.

Best Regards,

Osman

[Former Member]

If you dont have Fiscal Period in the Cube if 2LIS_02_ITM and 2LIS_02_SCL

is used ...how will YTD be calculated ?

I donot really know how to deal with other dates to calculate YTD.

[Former Member]

Hi Roberto,

After a lot of reading thru forums and hair pulling, I came to the conclusion that we'll need ABAP coding to provide transformation routines for <b>0PUR_C01 2LIS_02_ITM</b> && <b>0PUR_C01 2LIS_02_SCL</b> update rules.

<i>"Basically we want to derive field 0FISCYEAR (Fiscal year) and 0FISCPER3 (Posting period) from the field 0FISCPER (Fiscal year / period) where 0FISCPER (Fiscal year / period) is a field in the DataSource."</i> (I copied this from another forum since it was worded perfectly )

Once we have Fiscal Year and Posting period separated, then I like to try the solution posted on your forum <b>YTD w/o code</b>

to get YTD for our report.

Where you able to find a solution for <b>YTD w/o code</b>?

Thanks a lot

Best Regards,

Osman

[Former Member]

Fiscal Year and Posting Period can be derived from Fiscal Period without coding in the Cubes.

Add Fiscal Year and Posting Period to Cube and in UR just map - give source char for both as Fiscal Period.

Once you have Fiscal Year and Posting Period YTD can be derived w/o coding.

Create two variables with User Entry Mandatory on 0FISCALYEAR and 0FISCPER3.

Let the keyfigure be 0AMOUNT, User Entry Variables be ZFISCYR & ZFISCPER3

For YTD - > Create a Selection or RKF - > 0AMOUNT - > Restrict to - > 0FISCALYEAR - > Restrict to ZFISCYR - > Restrict to - > 0FISCPER3 - > Restrict to - > Change the dropdown on left to RANGES - > From January/001 to ZFISCYR.

You may have to go with Text Variables to display Names of Months and Years....post back once you are there.

[Former Member]

Hi Roberto,

Again, thank you for your prompt response.

Clarification on your comment since I did add them to the cube but I had limitations in the Source Char selection:

<b>Fiscal Year and Posting Period can be derived from Fiscal Period without coding in the Cubes.

Add Fiscal Year and Posting Period to Cube and in UR just map - give source char for both as Fiscal Period.</b>

If you refer to my earlier post, I mentioned the limitations I had to my update rules:

<i>

PUR_C01 2LIS_02_ITM - has the following choices for the Source Char

0DOC_DATE Document Date

0PSTNG_DATE Posting date in the document

0ENTRY_DATE Date on which the purchasing document was created

0VAL_END Validity period end

0VAL_START Validity Period Start

0PUR_C01 2LIS_02_SCL

0DOC_DATE Document Date

0PSTNG_DATE Posting date in the document

0SCL_DELDAT Planned delivery date of document schedule line

0SCHED_DATE Schedule line date

0ENTRY_DATE Date on which the purchasing document was created

0STAT_DATE Statistics date

0VAL_END Validity period end

0VAL_START Validity Period Start</i>

And since <b>Fiscal Year/Period</b> in the Time Charactersitc was mapped to "

Date on which the purchasing document was created"...For my two new additions to the cube - <b>Fiscal Year</b> &&<b> Posting period</b>, I mapped them to

"Date on which the purchasing document was created" also BUT no data came in even though "Automatic conversion" was check marked.

So I wanted to get some clarification on your comment since I can't do that.

thanks a lot

Best Regards,

Osman

[Former Member]

My post above is only assuming you have Fiscals populated for all data in the Cube.

For other dates I may be of no help

[Former Member]

Hi Roberto,

Thanks for clarifying that for me. I'll post back after I get ABAP help to filter out Fiscal Year && Posting Period from Fiscal Year/Period and have tried your solution. God willing it'll work and then i'll close the thread.

Thanks again

Best Regards,

Osman

Message was edited by:

Osman Baig