Skip to Content
0

Referencing one report against another

May 15, 2017 at 04:46 PM

77

avatar image

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Jyothirmayee A May 15, 2017 at 05:30 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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?

0
Jyothirmayee A May 16, 2017 at 02:46 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded