Autogenerate sales revenue?

[#1]

Hi,

I am working with an application that has the purpose to show the sales reporting for each sales person in our organization.

We receive an Excel spreadsheet from our sales organization with the following info:

Customer, Monthly sales amount, startdate, number of month.

For example:

Customer X, 1000 EUR, 2016-01-01, 12.

Customer Y, 2000 EUR, 2016-03-01, 2.

We want to create a monhtly sales pipeline from this information. The sales pipeline from these two customers should look like:

2016-01 - 1 000 EUR

2016-02 - 1 000 EUR

2016-03 - 3 000 EUR

2016-04 - 3 000 EUR

2016-05 - 1 000 EUR

and so on,.

How should the script look like so that the sales pipeline can be generated?

Startdate is always on the first day of the month. We also have the possibility to change the reporting if another format should be used.

[maxgro]

RESULT

SCRIPT

SET DateFormat='YYYY-MM-DD';

input:

load Customer, Replace(Sales, 'EUR', '') as Sales, Start, NumMonth inline [

Customer, Sales, Start, NumMonth

Customer X, 1000 EUR, 2016-01-01, 12

Customer Y, 2000 EUR, 2016-03-01, 2

];

Left Join (input)

load

  Customer,

  Start,

  Date(AddMonths(Start, iterno()-1)) as End

Resident input

While IterNo() <= NumMonth;

[#3]

Thank you for helping me with this - it worked perfect!

[jagan]

Hi,

You can also try KeepChar() like below

SET DateFormat='YYYY-MM-DD';

input:

load Customer, KeepChar(Sales, '0123456789', '') as Sales, Start, NumMonth inline [

Customer, Sales, Start, NumMonth

Customer X, 1000 EUR, 2016-01-01, 12

Customer Y, 2000 EUR, 2016-03-01, 2

];

[jagan]

Can also try Purgchar() similarly.

Regards,

Jagan.