Dates

[Former Member]

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

[Former Member]

Thanks, I will try the two options.

But, I want to make a small correction to my post -

The current sale amount calculation is actually returning the sum of sale amt for both the sale date and end of quarter date.

In other words, I want the sale amt for this month and last quarter in two different columns, but now it is summed up and shown in one column.

and the quarter sale amt is shown as zero.

Basically, the query returns the data for 2 dates but i am not able to separate the two data.....

[Former Member]

Can you post the where clause of your query?? The problem may be in the query so it displays 0.

[Former Member]

This is the where clause skeleton-

sale date:

where sale date = @sale date prompt or saledate = end of quarter calculation.

Does this give any idea?

[Former Member]

Well i feel the prompt is not going to get the sales measure for the previous quarter based on the explanation you had given for your design.

1. The quesry which extracts the sales based on the sales date is a stright forwards and your had done it right.

2. For the sales of previous quarter, you will have to refine your query,

recommendation to extract the previous quarter sales based on the sales date prompt condition created in universe,

 where to_char(sale date,'q') = to_char(@sale date prompt (condition),'q')-1

Use this condition as a or in the query condition panel.

[Former Member]

where sale date = @sale date prompt or saledate = end of quarter calculation.

I don't think that the condition you have mentioned is correct.

gave both these dates in the "in" clause of the sql 

Acc. to this stmt your where clause should be something like this Where Sales Date In (Date 1, Date 2) and should work fine.

Check your where clause by directly running the query on the DB and confirm that the genrated SQL is giving you the correct

result i.e. for the two dates.

Now when you are creating the variable at the report level you must have extracted prompt date using UserResponse fun

which will convert your date to string because of its return type. So, before comparison make sure that you are comparing the dates in the same type (String or Date) and format (MM/DD/YYYY or etc.) .

Regards,

Rohit