Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Thank you for helping me with this - it worked perfect!
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
];
Can also try Purgchar() similarly.
Regards,
Jagan.