Question:
Hi,
I need to classify the open order quantity into past due current due and future due on the basis of confirmed date comparing it with system date. So on any day when I compare these two dates, if the differrence is 0, then it is current due, if it is <0, then it is past due and if it is > 0, then it is future due. I want to calculate this at query level. Can somebody help me with a quick suggestion? I need this very urgently. Will it make a difference if I store system date in the cube on a daily basis and use the same for calculations in the query using formulas?
Thank you.
BWN
Answer:
this is usually done against a key date and one or two of the key date variables default to today's date (so you shouldn't need to supply today's date)
create a series of restricted key figures with date intervals as the restriction.
for current quantities restrict:
confirmed date >= key date
for 1 to 30 days late restrict:
confirmed date between [key date - 30 : key date -1]
for 30 to 60 days late restrict
confirmed date between [key date - 60 : key date -30]
and so on
by using key date you can slide your date around; what will no longer be current next week, stuff like that.
all of the variables, value range settings, and offsets are right there in the restriction dialog. good luck.
this is taught in BW305 and TBW20 (but it is right after lunch and most people are asleep)