How to give conditions in a BEx query designer

 The query designer formulas sometimes involve if then else kind of conditions or case statement type of logic. These type of logic can be easily given in the query designer, however the procedure is little tricky as you cannot write simple if statements in the query designer.

Scenario –

Suppose you have Amount and Net due date characteristics in the cube. The user gives the key date as query input.

Based on the difference between the net due date and the key date, the query should return whether the amount is

• Due (i.e. net due date is in future as compared to key date) or
• Overdue (i.e. the net due date is passed as compared to key date).

The query should also return the bucket information if the amount is overdue. Which means that the query should tell that the overdue amount falls in which bucket (>90, 61-90, 31-60 or 1-30).

These buckets are assigned based on the number of days calculated earlier which is the difference between the net due date stored in the cube and the key date entered by the user while query execution.

Also there exists in the transaction data, some data records where the net due date is zero, the query should not process such records.

This kind of scenario is mostly seen  in the Account receivables KPI.

To create such a query we can follow the below steps-

1. Drag amount field in the columns pane of the query designer
2. Create a calculated key figure. Define it as Days  = key date – net due date , drag it to the columns pane
3. Create one more calculated key figure - Days Valid  and define it as follows-

(‘Net Due Date’ <> 0) * 1 * ‘Days’

Save and drag it to the columns pane.

4. In the columns pane, right click and select formula, say Due/Overdue. Define it as follows-

((‘Days Valid’ <= 0) * 1 + (‘Days Valid’ > 0) * 2)

This formula will return '1' if days valid is less than or equal to zero and will return '2' if days valid is greater than zero.

In other words, the formula returns '1' if the net due date is in future or equals to key date and it returns '2' if the net due date is in the past.

Thus if the value of this formula is 1, the Amount is due and if the value of formula is 2, then the amount is Overdue.

5. Next define one more formula for calculating the bucket as follows-

((‘Days Valid’ >= 1) AND (‘Days Valid’ <=30)) * 1 + 

((‘Days Valid’ >= 31) AND (‘Days Valid’ <=60)) * 2 +

((‘Days Valid’ >= 61) AND (‘Days Valid’ <=90)) * 3 +

(‘Days Valid’ > 90) * 4

Here the formula returns 1 if 1-30 bucket, 2 if 31-60 bucket and so on.

6. Define a formula Due amount as follows-

NODIM ((‘Due/Overdue’ == 1) * Amount)

7. Define formula Overdue amount as follows-

NODIM ((‘Due/Overdue’ == 2) * Amount)

8. For each of the bucket, you can define a formula like below-

Overdue Amount in 1-30 bucket

NODIM (((‘Bucket’ == 1) and (‘Due/Overdue’ == 2)) * Amount)

Overdue Amount in 31-60 bucket

NODIM (((‘Bucket’ == 2) and (‘Due/Overdue’ == 2)) * Amount)

Overdue Amount in 61-90 bucket

NODIM (((‘Bucket’ == 3) and (‘Due/Overdue’ == 2)) * Amount)

Overdue Amount in >90 bucket

NODIM (((‘Bucket’ == 4) and (‘Due/Overdue’ == 2)) * Amount)

Now the query is ready with all the logic. Below will be a sample output of the query-

Key date	Net due date	Amnt	Days Valid	Due/Overdue	Bucket	Due Amnt	Overdue Amnt 1-30
01.02.2016	20.01.2016	100	12	2	1	_	100
01.02.2016	08.02.2016	190	-7	1	_	190	-