Query requirement

[Former Member]

Hi Guys,

Need your help. I have to make a Query. The scenario looks like this:

Report has rows: Sales doc no, start date, end date, cancellation date

Report has columns: Prev period, new bkng, Cancels, Expired, Current yr, Net, plan cost

Req

1) if cancellation date or end date has not yet happened, whichever date is later, then Current yr, Net, plan cost shud be populated

if cancellation date or end date has happened, whichever date is later, then Current yr, Net, plan cost shud not be populated and cancels amount shud be populated

2) if the start date is start of fiscal year i.e 10/1/2007 then, prior year eg 8/4/2006(end date) - sales doc shud be showed also but if there are two or more prior year sales doc with end date before 10/1/2007 then take the prior year sales doc with largest end date.

• note:- the 3rd point functionality is only done for start of fiscal yr that is october 10

and Expired, Prev period, shud be populated in prior year sales doc but no value for current yr , no value for plan cost - shud be populated.

3) If cancellation or end date has happend, then only a cancellation flag Y or N should be set

If current sales doc doesnot have cancellation date then flag is set to blank

If cancellation date or end date is not yet happened then flag is also set to blank

Set flag to Y if an Object 0Reason has value AR or else if has no value then set to N

4) If selection month eg 5/2007 and end dat eis 2/2007 then Net value shud show up in expired till end of fiscal year and a Truly expired should be set Y or N

if there is no future period for slaes doc in the table /cube then set it to Y

if there is future period for slaes doc in the table /cube then set it to N

5) A truly new booking Y or N flag shud be set if there is no prior sales doc i.e truly a new booking. The start date falls in the current fiscal year

Thanks

Rob

[former_member188975]

Hi Rob,

Have you started to attempt this yet? Is there a particular step that you got stuck on...what exactly are you looking for?

[Former Member]

Hi,

I have started a bit...but have doubts...I was thinking if you have idea how to do it so it will be helpful..

The 3rd point ...i can do ..its easy..

but rest i wanted to ask.

Regards

Rob

[former_member188975]

Well, for example on this one:

"if cancellation date or end date has not yet happened, whichever date is later, then Current yr, Net, plan cost shud be populated

if cancellation date or end date has happened, whichever date is later, then Current yr, Net, plan cost shud not be populated and cancels amount shud be populated"

You need to create formula variables of type replacement path for the dates (there are several posts that talk about this) and then compare it with the formula variable for the system date (this is provided by SAP). The result would be a True (1) or False(0) and appropriately you can create a formula to multiply the result of this decision by the KF you want to display or not display in the said condition.

Hope this helps...

[Former Member]

Hi,

If i send you screenshot of the Query, will you will able to help.

Send me your email address.

Regards

[Former Member]

Hi,

How do i compare? Suppose i have formula variable Canceldate and system date - wht is the formula variable for that? and how do i compare for if(canceldate has happend)?

Regards

Rob