Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Autogenerate sales revenue?

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.

Tags (1)
4 Replies
MVP
MVP

Re: Autogenerate sales revenue?

RESULT

1.png

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;

Not applicable

Re: Autogenerate sales revenue?

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

MVP & Luminary
MVP & Luminary

Re: Autogenerate sales revenue?

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

];

MVP & Luminary
MVP & Luminary

Re: Autogenerate sales revenue?

Can also try Purgchar() similarly.

Regards,

Jagan.