Skip to Content
avatar image
Former Member

Listing plants with no sale data on yesterday

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 27, 2015 at 05:39 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • Oct 26, 2015 at 02:57 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 27, 2015 at 05:57 AM

    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

    Add comment
    10|10000 characters needed characters exceeded