Date difference calculation with user input variable

[former_member685419]

Hi,

I have the following requirement in BEx:

The user inputs a date value and I have to calculate the difference between the date entered by the user with the Due Date in the cube, to determine the number of backlog days. Basically, I need to calculate Past Due in number of days. I need to display the Amount due for the Time buckets like 1 -10 days, 11 -30 days.

The report output has to be in the format below:

Columns:

1 u2013 10 days Past Due $100.00

11 u2013 30 days Past Due $15.00

Rows:

Customer#

The cube has data from Jan 2005 to June 2008.

For Eg: User Input Date = 04/30/2008.

It should compare the input date to the due date for each record in the cube.

Any suggestions are appreciated.

[Former Member · Accepted solution]

Hi Kumar,

We have developed simar reports in our previous projects.

You need to follow the following steps.

1. Create a new restricted key figure globally on Past Due $100. Restriction details I will let you know in the ensuing steps.

2. Create another restricted key figure globally on Past Due $15. Restriction details I will let you know in the ensuing steps.

3. Create a Z varuable on Calday and offset it -1 (use the button next to exclude button).

4.Create another Z varuable on Calday and offset it -10 (use the button next to exclude button).

5. Create another Z varuable on Calday and offset it -11 (use the button next to exclude button).

6. Create another Z varuable on Calday and offset it -30(use the button next to exclude button).

7 Restrict the RKF past due 100$ (defined in sl 1)between the variable range defined in 3 and 4.

8. Restrict the RKF past due 15$ (defined in sl 2) between the variable range defined in 5 and 6

Now drag and drop those two RKF to column and you will get the desired result in C1 and C2 as follows:

Columns:

C1 :1 u2013 10 days Past Due $100.00

C2 :11 u2013 30 days Past Due $15.00

If the above info serves your purpose, please reward me with the point.

Regards,

Subha

[former_member685419]

Thanks for all ur valuable replies

[Former Member]

You do it this way:

Create a restricted Key figure for the key figure you want to measure and use the Due Date field and restrict it by the variable the user enters and do an offset (-10,0) on this variable with the range. This will give you past due 10 days and like wise you do for others and display in the report one after the other.

thanks.

Wond

[former_member685419]

Question not answered

[former_member685419]

The user entered date value has to be compared with every record of the cube(every due date)and display the difference in days.

Edited by: kumar on Jul 30, 2008 8:52 PM