Difference in Measure based on User Response

[system]

Hi All,

I need to capture the difference in a measure field based on user response.

I have a measure Field: Transaction_Amount
And a dimension field: Reporting_Date

User enters 2 reporting dates in the prompt: One for Current Date (Not necessarily Sysdate) and One date for MTD Analysis.

Let’s say for an example User enters ‘31st Aug, 2005’ as Current date and ‘31st July, 2005’ as MTD Analysis date.

I am able to capture these two dates. Now my problem is I am not able to capture the amount based on these dates:

Following are the formula I am using:
Current_Date = UserResponse(“Current” ,“1 Select Current Reporting Date”)
MTD_Date = UserResponse(“Current” ,“2 Select MTD Reporting Date”)

Till here it works fine.

Now for capturing the Amount:

Tran_Amt_Current = (<Transaction_Amt> Where (<Reporting_Date> = <MTD_Date>))

Tran_Amt_MTD = (<Transaction_Amt> Where (<Reporting_Date> = <MTD_Date>))

The above two formula give me error. can’t I do this way? If I specify the hard-coded dates, then my report won’t work if the user changes the dates?

Any help in this regard please? I am facing a very hard time for this

Thanks
Saswati

---

saswati (BOB member since 2005-06-13)

[system]

Please, take a look at the FAQ Reporter :
Why do I get an error when I try to Sum() Where ( > 2000)?

---

Andreas (BOB member since 2002-06-20)

[system]

try,

Tran_Amt_Current = If (<Reporting_Date> = <MTD_Date>) Then <Transaction_Amt>

Tran_Amt_MTD = If(<Reporting_Date> = <MTD_Date>)Then <Transaction_Amt>

---

KhoushikTTT (BOB member since 2005-02-24)

[system]

Hi NewyorkBaasha, Thanks a lot. That worked

Andreas, Actually I have seen that post and have tried that. That didn’t work. In my query I had used as per that instructions only.

Thank you all for the replies.

Regards
Saswati

---

saswati (BOB member since 2005-06-13)