Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Comparing total sales budgets against actual sales.

I am trying to created a table thats compares my budgeted sales and margin to the actual sales and margin by YearMonth for branches and product groups.

I have loaded all the data but have found that where there is a budget for either product group or month and there are no sales for the month or product group the budget amount is not included.

I think the issue is from how the tables are linked (the budgets being linked to the sales). But I am unsure how to fix.

What I want to show is the total budget amount regardless of whether there are sales or not.

/******************** Product Budgets *******************/

SalesBudgettemp:

Directory;

CrossTable (YearMonth, SalesBudget, 2)

LOAD *

FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is NettBudget);

SalesBudget:

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonth,'YYYYMM')) AS BranchBudgetKey,

SalesBudget

Resident

SalesBudgettemp;

Drop Table SalesBudgettemp;

GMBudgetTemp:

Directory;

CrossTable (YearMonthGM , GMAmount, 2)

LOAD *

FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is GM%Budget);

GMBudget:

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonthGM,'YYYYMM')) AS BranchBudgetKey,

GMAmount

Resident

GMBudgetTemp;

Drop Table GMBudgetTemp;

/*************** Links to the Sales Details table *****************

SalesLinking:

Left Join (SalesDetails)

load

MasterorderKey,

InvLineNumber,

AUDITCENTRE &'-'& ApplyMap('ProdGroup_Map',STOCKCODE,'MISSING')&'-'& MonthStart(InvoiceDateID) AS BranchBudgetKey,

CustomerGroup &'-'& ApplyMap('ProdGroup_Map',STOCKCODE,'MISSING') &'-'& MonthStart(InvoiceDateID) AS GroupRebateKey,

Resident SalesDetails;

1 Solution

Accepted Solutions
Not applicable

Comparing total sales budgets against actual sales.

I have resolved my earlier question regarding missing budget figures.

I used CONCATENATE to load my budget to sales detail and added bracnh / product group and various year month and dateID keys. This is now bring through the complete budget regardless of any product theat do not have sales.

RE: new script below















//********************************* Product Budgets ***********************************

SalesBudgettemp:

Directory;

CrossTable(YearMonth, SalesBudget, 2)

LOAD *

FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is NettBudget);

SalesBudget:

concatenate (SalesDetails)

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonth,'YYYYMM')) AS BranchBudgetKey,

YearMonth,

Date#(YearMonth,'YYYYMM') AS BudgetDateID,

Date#(YearMonth,'YYYYMM') AS YEARMONTHID,

Dual(Month(Date#(YearMonth,'YYYYMM')),Month(addmonths((Date#(YearMonth,'YYYYMM')),-3))) AS MONTHNAMEID,

Date#(left(YearMonth,4),'YYYY') AS YEARID,

YearName((Date#(YearMonth,'YYYYMM')),0,$(vBudStartMonthFY)) AS YEARFYID,

Branch AS AUDITCENTRE,

PRODUCTGROUP AS PRODUCTGROUP,

SalesBudget

Resident SalesBudgettemp;

Drop Table SalesBudgettemp;

GMBudgetTemp:

Directory;

CrossTable(YearMonthGM , GMAmount, 2)

LOAD * FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is GM%Budget);

GMBudget:

concatenate (SalesDetails)

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonthGM,'YYYYMM')) AS BranchBudgetKey,

YearMonthGM,

Date#(YearMonthGM,'YYYYMM') AS BudgetDateID,

Date#(YearMonthGM,'YYYYMM') AS YEARMONTHID,

Dual(Month(Date#(YearMonthGM,'YYYYMM')),Month(addmonths((Date#(YearMonthGM,'YYYYMM')),-3))) AS MONTHNAMEID,

Date#(left(YearMonthGM,4),'YYYY') AS YEARID,

YearName((Date#(YearMonthGM,'YYYYMM')),0,$(vBudStartMonthFY)) AS YEARFYID,

Branch AS AUDITCENTRE,

PRODUCTGROUP AS PRODUCTGROUP,

GMAmount

Resident GMBudgetTemp;

Drop Table GMBudgetTemp;

//********************************* Sales Header **********************************

SalesHeader:

Left Join (SalesDetails)

LOAD

"SAHED_ARMasterFileId" AS ARMASTERID,

if("SAHED_SaleTypeFlag" = 'I' , 'Invoice', 'Credit') AS SalesType,

"SAHED_SaleTypeFlag" AS SalesTypeKey,

"SAHED_AuditCentre" AS AUDITCENTRE,

"SAHED_AuditNo" AS AUDITNO,

"SAHED_SuffixNo" AS SUFFIXNO ,

autonumber("SAHED_ARMasterFileId"&'-'&"SAHED_AuditCentre"&'-'&"SAHED_AuditNo") AS MasterorderKey,

"SAHED_AccountId" AS CustomerCode,

autonumber("SAHED_ARMasterFileId"&'-'&"SAH ED_AccountId") AS MasterAccountKey,



"SAHED_InvoiceDate"

Date#("SAHED_InvoiceDate",'YYYYMMDD') AS InvoiceDateID,

Date#(left("SAHED_InvoiceDate",6),'YYYYMM') AS YEARMONTHID,

Date#(left("SAHED_InvoiceDate",4),'YYYY') AS YEARID,

"SAHED_Salesperson" AS Salesperson,



if("SAHED_ClosedFlag" = 'Y', 'Posted','Not Posted') AS SaleStatus,

"SAHED_PNREF";

SQL SELECT *

FROM "SAHED_Sales_Header"

where left("SAHED_InvoiceDate",4)>=2008 AND "SAHED_ARMasterFileId" ='ARCOY' AND "SAHED_ClosedFlag" <> 'X' AND "SAHED_ClosedFlag" <> 'C';







//************************* Links to the Sales Details table ********************************************

SalesLinking:

Left Join (SalesDetails)

load MasterorderKey,

YearName(InvoiceDateID,0,$(vStartMonthFY)) AS YEARFYID,

Dual(Month(InvoiceDateID),Month(addmonths(InvoiceDateID,-3))) AS MONTHNAMEID

Resident SalesDetails;

2 Replies
chooco_co
Not applicable

Comparing total sales budgets against actual sales.

hi,

that's same as my problem..

Not applicable

Comparing total sales budgets against actual sales.

I have resolved my earlier question regarding missing budget figures.

I used CONCATENATE to load my budget to sales detail and added bracnh / product group and various year month and dateID keys. This is now bring through the complete budget regardless of any product theat do not have sales.

RE: new script below















//********************************* Product Budgets ***********************************

SalesBudgettemp:

Directory;

CrossTable(YearMonth, SalesBudget, 2)

LOAD *

FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is NettBudget);

SalesBudget:

concatenate (SalesDetails)

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonth,'YYYYMM')) AS BranchBudgetKey,

YearMonth,

Date#(YearMonth,'YYYYMM') AS BudgetDateID,

Date#(YearMonth,'YYYYMM') AS YEARMONTHID,

Dual(Month(Date#(YearMonth,'YYYYMM')),Month(addmonths((Date#(YearMonth,'YYYYMM')),-3))) AS MONTHNAMEID,

Date#(left(YearMonth,4),'YYYY') AS YEARID,

YearName((Date#(YearMonth,'YYYYMM')),0,$(vBudStartMonthFY)) AS YEARFYID,

Branch AS AUDITCENTRE,

PRODUCTGROUP AS PRODUCTGROUP,

SalesBudget

Resident SalesBudgettemp;

Drop Table SalesBudgettemp;

GMBudgetTemp:

Directory;

CrossTable(YearMonthGM , GMAmount, 2)

LOAD * FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is GM%Budget);

GMBudget:

concatenate (SalesDetails)

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonthGM,'YYYYMM')) AS BranchBudgetKey,

YearMonthGM,

Date#(YearMonthGM,'YYYYMM') AS BudgetDateID,

Date#(YearMonthGM,'YYYYMM') AS YEARMONTHID,

Dual(Month(Date#(YearMonthGM,'YYYYMM')),Month(addmonths((Date#(YearMonthGM,'YYYYMM')),-3))) AS MONTHNAMEID,

Date#(left(YearMonthGM,4),'YYYY') AS YEARID,

YearName((Date#(YearMonthGM,'YYYYMM')),0,$(vBudStartMonthFY)) AS YEARFYID,

Branch AS AUDITCENTRE,

PRODUCTGROUP AS PRODUCTGROUP,

GMAmount

Resident GMBudgetTemp;

Drop Table GMBudgetTemp;

//********************************* Sales Header **********************************

SalesHeader:

Left Join (SalesDetails)

LOAD

"SAHED_ARMasterFileId" AS ARMASTERID,

if("SAHED_SaleTypeFlag" = 'I' , 'Invoice', 'Credit') AS SalesType,

"SAHED_SaleTypeFlag" AS SalesTypeKey,

"SAHED_AuditCentre" AS AUDITCENTRE,

"SAHED_AuditNo" AS AUDITNO,

"SAHED_SuffixNo" AS SUFFIXNO ,

autonumber("SAHED_ARMasterFileId"&'-'&"SAHED_AuditCentre"&'-'&"SAHED_AuditNo") AS MasterorderKey,

"SAHED_AccountId" AS CustomerCode,

autonumber("SAHED_ARMasterFileId"&'-'&"SAH ED_AccountId") AS MasterAccountKey,



"SAHED_InvoiceDate"

Date#("SAHED_InvoiceDate",'YYYYMMDD') AS InvoiceDateID,

Date#(left("SAHED_InvoiceDate",6),'YYYYMM') AS YEARMONTHID,

Date#(left("SAHED_InvoiceDate",4),'YYYY') AS YEARID,

"SAHED_Salesperson" AS Salesperson,



if("SAHED_ClosedFlag" = 'Y', 'Posted','Not Posted') AS SaleStatus,

"SAHED_PNREF";

SQL SELECT *

FROM "SAHED_Sales_Header"

where left("SAHED_InvoiceDate",4)>=2008 AND "SAHED_ARMasterFileId" ='ARCOY' AND "SAHED_ClosedFlag" <> 'X' AND "SAHED_ClosedFlag" <> 'C';







//************************* Links to the Sales Details table ********************************************

SalesLinking:

Left Join (SalesDetails)

load MasterorderKey,

YearName(InvoiceDateID,0,$(vStartMonthFY)) AS YEARFYID,

Dual(Month(InvoiceDateID),Month(addmonths(InvoiceDateID,-3))) AS MONTHNAMEID

Resident SalesDetails;