Discussion board where members can get started with QlikView.
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.
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.
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)
Date(AddMonths(Start, iterno()-1)) as End
While IterNo() <= NumMonth;
Thank you for helping me with this - it worked perfect!
You can also try KeepChar() like below
load Customer, KeepChar(Sales, '0123456789', '') as Sales, Start, NumMonth inline [
Can also try Purgchar() similarly.