Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

add target sales by salesperson per date with excel

Hello guys,

I need to add target amount which is ordered by salesperson and date (Month and Year)

How can I import this ? My targets are in Excel with

Salesperson, Date,

Target_Month, Target_Year (  ---> Targets are in Amount)

The other basic table:

DocNo,

Salesperson, Date, AccountNo, Amount,.

Tags (2)
3 Replies
florentina_doga
Contributor III

Re: add target sales by salesperson per date with excel

table:

load DocNo,Salesperson, Date, AccountNo, Amount,

MonthStart(Date) as  Target_Month,

YearStart(Date) as Target_Year

from sursa.qvd;

Concatenate (table)

load Salesperson, Date, Target_Month, Target_Year

FROM

xxx.xlsx

(ooxml, no labels, table is Sheet1);

Not applicable

Re: add target sales by salesperson per date with excel

Well, my fault..

I want to add the targets by month and years per salesperson like

SalespersonDateMonthStart(Date) as Target_Month ?? in AmountYearStart(Date) as Target_Year ?? in amountHeader 5
AA01.01.20151001200
AA02.02.20151001200
AA03.03.20151001200
BB01.01.20152002400
BB02.02.200152002400

at the end,  I want to get this

SalespersonDateMonthYearsum(amount)sum(target_amount)?
AA01.01.2015January2015130100
AA02.02.2015February201550100
AA03.03.2015....70100
TOTAL250300
Not applicable

Re: add target sales by salesperson per date with excel

Generally Targets are more likely Monthly or Yearly. In your file have both seems to be both are same. If you have different month & year targets you can load two targets separately in same tables.

// First Load the Monthly Targets.

MonthTargets:

LOAD

     SalePerson & '|' &MonthStart(Date) AS %Key_TargetMonthly,

     Target_Month

From TargetFile;

YearTargets:

LOAD

     SalePerson & '|' &YearStart(Date) AS %Key_TargetYearly,

     Target_Year

From TargetFile;

Create the %Key_TargetMonthly & %Key_TargetYearly in you fact table with same functions.


Please note that, year targets on Year level & Month target on Month level.

Community Browser