Listing plants with no sale data on yesterday

[m_gokdemir_m]

Hi Experts,

I try to create a webi report using bex olap conection from bw. The table shows the plants in which there is no sales yesterday.

(BO   4.1. sp 4)

(BEX 7.3. sp 9)

For example, today is : 26.10.2015 and sample data(Query1) is below.

Plant	Date	Quantity
1001	25.10.2015	10
1002	25.10.2015	10
1003	24.10.2015	15
1004	25.10.2015	38

How can I display 1003 value?

I can use "result from another query" option but this does not work with olap connection.

[Former Member · Accepted solution]

Hi,

Follow these steps.

1. Create a Variable (Detail) to get system Date - 1 .

2. Create another variable , say Flag ; Flag = 0 if Date (Column 2 of table)  = variable else Flag  = 1.

2. On WebI vertical table , add a filter on Flag ; where Flag = 1.

Please let me know if it works.

Thanks,

Rohit Garg

[m_gokdemir_m · Accepted solution]

When the flag is dimension this option does not work. However, when I define the flag as measere and write the code as "=Sum(If ([Date] = [Yesterday]) Then 1 Else 0" it works after filtered as "flag EQ 0" value of this measure.

[chandan_praharaj]

Hi Muhammad,

You can restrict the data in Bex level itself, as the query will be more optimised by that way.

Create a variable to calculate the 'SY-DATUM - 1'

Use the variable as Global filter restriction on Date.

Regards,

CPJ

[amitrathi239]

Hi,

can you explain more in detail.how are you calculating there is no sales for the plant?

based on the quantity value where quantity is zero?

according to your table there is a quantity for 1003.for me it means there is a sale for plant 1003.

Amit

[m_gokdemir_m]

The table data is my source table. I want to show plants where there is no sale yesterday.