I have a serious problem in calculating the previous quarter end date given the current date parameter.
This is the senario:
I have a date prompt to get the sale date and the report should display the data (total sales amount) for that sale date plus the previous quarter end date.
What I did:
I created a propmt at the universe level to get the date and calculated the previous quarter date in the sql syntax and gave both these dates in the "in" clause of the sql to retrieve data for both the dates. Now my universe query has data for 2 sale dates.
Then in the webi report I created 2 variables - var sale date = max(date prompt) and var quarter date = min(date prompt).
The variables are also created without any problem.
Next, to calculate the sales amt, I am doing var saleamt = if(sale date =max(date prompt);saleamount;0) and
var quarter sale amt = if (sale date =min(date prompt);saleamount;0).
My var sale amount returns the sales amt for that date but my quarter sale amt is not calculating the sale amt it is returning zero...
Can someone say why it could be?? is there smthng wrong with my var quarter saleamt formula?? how to trouble shoot?? pls advice