C4C reporting extract customers not ordered in past

[sanBod]

Hello All,

I was going through couple of blogs, but could not find the relevant solution for this. Please help.

We have a requirement to show the customers list who have not placed Sales ordered since 6 months.

I have done combined "Account" and "Sales Order" Datasources, but result is bringing all the entries. Is there any way to achieve this please?.

Thank you.

[sanBod]

Hi Dear C4C team,

Anyone has idea about this C4C reporting to achieve this requirement?

Regards, San

[former_member497128]

Hi santhosh_bodduna,

There is one more way (with some customization)

The logic would be "first find the max value for created on for each Account and then filter data which was created before a particular date".

Here in this case, created on is not a key figure, so we can not use it in calculated key figure and find the max value, so please use SDK and use this characteristic as key figure and then apply filter as less than 1st Jan 2020, this will give you all the customers who have not created any order on/after 1st Jan 2020.

Please try this as well and let us know if this helps.

Regards,

Saurav Kumar

[Bharathraj_A]

Hi santhosh_bodduna,

Can you try this
1.Create a new data source - left outer join of "Account Details" and "Sales Order Header" Data source.
2.Create Report using the newly created data source.
3.Based on "created on" field in sales order, you can use the last six months as variable or filter criteria
4.To show only the accounts, where the sales order is not created, you can restrict the sales order column by showing only "#"(Not assigned). Use filter or selection criteria to restrict it.

Hope this helps!

Regards,

Bharathaj

[former_member497128]

Hi Bharath,

I am not sure if below will work to fulfill the requirement as

-> if we filter on created on field with last six months as variable, it will give all the customers who have created orders in last six months but here requirement is to check the details of customer who have not created orders since last six months.

-> If we apply filter on sales order as #, it will list all the customer who are not associated with any sales order but again in this case also the requirement is not fulfilled.

There are few more things which i did try:

-> There is no need to join sales order header data source with account details unless we need other details of customers as few account details are also there in sales order header data source

-> If we can apply filter on created on like "less than 1st Jan 2020". It will list out all orders created before 1st Jan 2020 which means the customer associated with these orders might not created orders after 1st Jan.

Here there are two important points:

1) We have to give fixed value as variable (like 1st Jan)

2) It does not guarantee that customer have not created any orders after 1st Jan.

Lets understand by below example:

Orders created before 1st Jan are 101 (created by customer C1),102(created by customer C2),103(created by customer C1) Orders created after 1st Jan are 104(created by customer C1),105(created by customer C3),106(created by customer C4)

so as per requirement, we should only get details for customer C2 as he is the only customer who has not created orders after 1st Jan but if we apply filter on created on as "less than 1st Jan" then it will list customer C1 and C2 both while C1 has already created orders after 1st Jan as well.

You can also check and let me know if i am missing something here and if there is any other way to fulfill the requirement.

Regards,

Saurav Kumar

[sanBod]

Hello Saurav,

Exactly tried earlier. By using # in Sales order, we get also the full Accounts in DS1 for Combined Datasource.

If we don't combine and use only Order DataSource, then whatever the Accounts are there in Order DS will show up. In this case, if any Account not even placed a single order will never show up.

We need to use 2 datasources, but at some point we need to filter out Accounts (which are in DS1) to be filtered out IF they are in Orders (which are in DS2).

Please let me know, if anyone have other idea where to filter out Accounts when comparing the Datasources OR when displaying the output.

Thank you,

regards,

Santosh

[Bharathraj_A]

Hi Saurav/Santhosh,

Thanks for pointing out.

Yes you are correct.I tested in my system.It is not working as expected.
Will check for other possibilities.

Regards,
Bharathraj

[former_member497128]

Hi santhosh_bodduna,

Please find the solution as below:

Steps to follow:

1) Create a new restricted key figure based on key figure "Counter" and fixed value for characteristic "Created On".

a) If you want, you can give value as greater than "1st Jan 2020 (a fixed date)" if you want to check all customers who have not created any sales order after 1st Jan 2020.

b) or you can select value as equal to "Last 6 Months to Date" if you want to list out all the customers who have not created any order from last 6 months.

2) Run the Report, save the desired view (for example, add account, sales order, created on and newly created key figure.

3) Create new simple condition using newly created key figure and characteristic as "Account" and put rule as equal to ZERO.

This will give you the list of customers as per your requirement. Please try the same and let us know in case of any further concerns.

NOTE: We have tried above solution on report created on data source "Sales Order Header".If you join this data source with Account, then please check how counter is behaving as counter is playing the big role here.

Regards,
Saurav Kumar