Skip to Content
avatar image
Former Member

Referencing one report against another

Hi, I have a report I'm trying to put together that is fairly complex (for me, anyway).

I have a report that is pulling from two separate queries - one that contains historical sales data, and one that contains currently open orders. What I'm trying to do is make my report only show line items where a specific customer has ordered a specific item for the first time. The report is displaying information from the currently open orders query. So, basically, I want to make currently open orders show only if the customer/item combination does not appear in the historical sales data. I can easily do this in Excel after exporting the data, but I need to automate this in BI because it is being requested every day.

Any help is greatly appreciated. Thanks!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 15, 2017 at 05:30 PM

    Hi,

    Hope you have merge on Customer.

    There are multiple ways:

    1. Verify if any sales towards that Customer is 0

    2. Find out by the Created Date and Sales

    3. Compare the Customer ID's with Previous Customer ID's Like =If (Not(IsNull(Count([customer_id)])))And IsNull(Previous(Count([customer_id]))) Then [customer_id]

    I'm just giving my options to find out new customer from a data set but has not tried.

    You can post sample data how it looks like.

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks - fortunately I do have a merge on the customer between the previous orders query and the current orders query. I can give this a try but I also need to find a way to get it to look at customer/item combination so I guess I can make some kind of string as a unique identifier.

      My report has 2 tabs - one with the historical orders and one with the new orders. Is there a way to put something in the new orders report, where I can use my customer/item combination and tell it that if it appears in the historical orders report to exclude it from the new orders report?

  • May 16, 2017 at 02:46 PM

    Hi,

    Create a table and brings in the objects from both the tables,

    Per say, if you have Customer Name, Sales ID, Type, Creation Date, Order Date, SHipped addr, Price

    Just bring in the Merged Customer ID and place it before the Customer Name, this way table will only show the customers that are appears in both tables, Just like Inner join.

    Start with this analysis and then you will get there as you go. :)

    Keep posting your analysis.

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded