topic Re: Sum at load (script) level in QlikView

Sum at load (script) level

chiso_chiso — Wed, 25 Mar 2015 07:54:01 GMT

Gurus,

I have an excel document with MONTHLY TARGETS per CHANNEL per PRODUCT, a qvd with DAILY SALES numbers per CHANNEL per PRODUCT. I have loaded separately by renaming some of the fields to avoid SYNC Key. I have a calendar script separate. For some reason my associations are not working as I am getting higher budget numbers upon month selection.

Kindly assist

Re: Sum at load (script) level

MK_QSL — Wed, 25 Mar 2015 07:55:28 GMT

Either provide sample data or your script or sample application...Thanks

Re: Sum at load (script) level

chiso_chiso — Wed, 25 Mar 2015 08:06:03 GMT

Below are my scripts;

Sales:

LOAD

ContractNumber,

Status as [Contracted Status],

StatusDate,

DateOfSale,

Date(Floor([DateOfSale])) as [Sales_Date],

Time(Frac([DateOfSale])) as [Sale Created Time],

Week([DateOfSale]) as Week,//[Sale Created Week],

Month([DateOfSale]) as Month,

Year([DateOfSale]) as Year,

BusinessUnitCode,

Channel,

Product

FROM

(qvd);

Budget201415:

LOAD BusUnitCode,

Product as ProductType,

Channel,

Financial_Year,

BudgetDate,

Date(Floor([BudgetDate])) as NewDate,

Month([BudgetDate]) as Month,

Year([BudgetDate]) as Year,

Budget_Vol

FROM

(ooxml, embedded labels, table is Sheet1);

//FiscalCalendar:

UNQUALIFY Sales_Date, NewDate;

SET vFiscalYearStartMonth = 7;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

Date as Sales_Date,

Date as NewDate,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Day(Date) as Day,

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear, // Fiscal Calendar Year

'Week'&Ceil(Day(Date)/7) as MonthlyWeek;

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

drop field Sales_Date from FiscalCalendar;

Re: Sum at load (script) level

jopmoekotte — Wed, 25 Mar 2015 08:47:46 GMT

Hello Harrison,

What you should do is create a key field to couple your budget with your sales, so that specific budgets are linked with respective sales of a channel, product and timeframe. This should be done on the smallest possible timeframe, which is month (since you have monthly targets, and you want all sales in a month to correspond to that budget).

The calendar you built also links to multiple fields, I suggest you link the Calendar to sales date. This way you can click any date/month/year etc. and get the corresponding sales of that date, which links to the corresponding budget(s).

Something as follows would suffice:

Sales:

LOAD *,

Product &'_'& Channel &'_'& Sales_year &'_'& Sales_month as Key

;

LOAD

ContractNumber,

Status as [Contracted Status],

StatusDate,

DateOfSale,

Date(Floor([DateOfSale])) as [Sales_Date],

Time(Frac([DateOfSale])) as [Sale Created Time],

Week([DateOfSale]) as Week,//[Sale Created Week],

Month([DateOfSale]) as Sales_month,

Year([DateOfSale]) as Sales_year,

BusinessUnitCode,

Channel,

Product

FROM

(qvd);

Budget201415:

LOAD *,

ProductType &'_'& Budget_Channel &'_'& Budget_year&'_'& Budget_month as Key

;

LOAD

BusUnitCode,

Product as ProductType,

Channel as Budget_Channel,

Financial_Year,

BudgetDate,

Date(Floor([BudgetDate])) as NewDate,

Month([BudgetDate]) as Budget_month,

Year([BudgetDate]) as Budget_year,

Budget_Vol

FROM

(ooxml, embedded labels, table is Sheet1);

//FiscalCalendar:

//UNQUALIFY Sales_Date, NewDate;

//SET vFiscalYearStartMonth = 7;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD *,

Date as Sales_Date,

//Date as NewDate,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

;

LOAD *,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Day(Date) as Day,

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear, // Fiscal Calendar Year

'Week'&Ceil(Day(Date)/7) as MonthlyWeek

;

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

//drop field Sales_Date from FiscalCalendar;

Hope this helps.

Kind regards,

Jop

Re: Sum at load (script) level

chiso_chiso — Wed, 25 Mar 2015 09:10:22 GMT

Thanks JopMoekotte

I have applied this solution as is and the result are 2 syn keys as per attached. The other thing on selecting a month is only returning my sales values for that month and not on Budget values.

Re: Sum at load (script) level

jopmoekotte — Wed, 25 Mar 2015 09:59:51 GMT

Hey Harrison,

You should rename the field BusinessUnitCode in either table so they don't link on the field.

In my example I named the field 'BusinessUnitCode' in 1 table and 'BusUnitCode' in the other.

Then you should do the same for month and year, as you can see in my example, I renamed the Month and Year in the sales table to 'Sales_month' and 'Sales_year'.

You want to link the Budget and Contracts on Product, Channel, year and month (that is what the field Key is for).

All other fields should have a name that are not similar between the tables.

You want to link the Contracts and FiscalCalendar on Sales_Date, so rename the month and year in the Sales table. For example to: Sales_month and Sales_year.

Kind regards,

Jop

Re: Sum at load (script) level

chiso_chiso — Wed, 25 Mar 2015 13:03:36 GMT

Thanks JopMoekotte, so far so good. I have put in two table objects one for Budget all fields and other for Sales all fields. On Clear all, both tables get populated, however when I make selections, from a selection list of products only one table gets populated.

How could that be?

Re: Sum at load (script) level

chiso_chiso — Thu, 26 Mar 2015 02:54:35 GMT

Thanks JopMoekotte I got it

Re: Sum at load (script) level

Thu, 02 Apr 2015 07:35:21 GMT

Hi Harrison,

Its better you keep one table for budget and sales and link it to the calendar using a comman date field.

Simple star schema approarch where have one fact table and multiple dimension tables.

Regards

Nidhin