Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've run into a bit of a snag, and I could use some advice. A little background on my QVW is as follows. I'm trying to create a customer dashboard with the goal to be able to see all the relevant information regarding that customer for any given period of time.
The tables that I have linked together are:
- Accounts Receivables
- Deposits
- Credits/Discounts
- Credit Info
- Sales
Each of these tables has their own date information. For example, Sales has a sales totals for each customer for every month. Accounts Receivables totals for every outstanding invoice as of the date the data was run. The same for Credit Info, Credit/Discounts, and Deposits.
So far for my reporting, I've only needed to use aggregate totals as of the date I've refreshed the QVW, however with the addition of sales data, there are times that I'd need to see sales data as of specific dates in the past.
So far, I've been able to make each date field on the tables unique, however I'm running into the problem where if I wanted to see the relevant customer data as of let's say as of 12/31/2015, I won't be able to isolate that date for all tables simultaneously with a date selection as a filter.
Would a possible solution be to use a variable which is then used as a filter for all tables?
Here is what my star schema looks like.
CustomerMaster:
LOAD
*,
ApplyMap('ARSpecialistMAP2',[Customer #],[AR Specialist Temp]) as [AR Specialist],
ApplyMap('CreditCombinedNameMAP',[Customer #],[Customer Name (Short)]) as [Combined Name (Credit)],
ApplyMap('DSOCombinedNameMAP',[Customer #],[Customer Name (Short)]) as [Combined Name (DSO)];
LOAD
*,
[Customer #] & ' ' & [Customer Name (Short)] as [Customer Name (Long)],
ApplyMap('CombinedNameMAP',[Customer #],'OTHER') as [Combined Name],
ApplyMap('CombinedNameMAP',[Customer #],'OTHER') as [Combined Name 2],
ApplyMap('AccountTypeCorrectionsMAP',[Customer #],[Account Type Old]) as [Account Type],
ApplyMap('DomesticMAP',[Customer #],'Y') as [BBC],
ApplyMap('GovernmentMAP',[Customer #],'No') as [Government],
ApplyMap('ARSpecialistMAP1',[AR Specialist Old],[AR Specialist Old]) as [AR Specialist Temp],
if([Credit Limit]=0,'1) 0',
if([Credit Limit]>0 and [Credit Limit]<=25000,'2) 1 - 25K',
if([Credit Limit]>25000 and [Credit Limit]<=250000,'3) 25K - 250K',
if([Credit Limit]>250000 and [Credit Limit]<=500000,'4) 250K - 500K',
if([Credit Limit]>500000 and [Credit Limit]<=2500000,'5) 500K - 2.5M',
if([Credit Limit]>2500000,'6) 2.5M+'
)))))) as [Credit Range],
if([Customer #]=[Parent ID],'Parent','Child') as [Parent],
Date(Monthstart(MakeDate(Year([DateSetupComplete]), Month([DateSetupComplete]), Day([DateSetupComplete]))),'MM/YYYY') as [MonthSetupComplete],
if([DateSetupComplete]='03/21/2015','Yes','No') as [Initially Added],
if([Repayment Terms]='Prepay Net','Prepay','Terms') as [Term Type];
LOAD
[Internal ID],
[Customer #],
[Repayment Terms],
[Customer Name] as [Customer Name (Short)],
[Repayment Terms] as [Repayment Terms 2],
[Credit Hold],
[Credit Limit],
[Account Type] as [Account Type Old],
ApplyMap('CustomertypeCorrectionsMAP',[Customer #],[Customer Type]) as [Customer Type],
ApplyMap('SalesCodeCorrectionsMAP',[Customer #],[Sales Code]) as [Sales Code],
[AR Specialist] as [AR Specialist Old],
[Inactive],
[Parent ID],
Date(MakeDate(Year([Date Created]), Month([Date Created]), Day([Date Created])),'MM/DD/YYYY') as [DateSetupComplete],
[Credit Code],
[Sales Rep 1] as [Sales Rep]
FROM
[\\luckybrand.net\shares\shares\Accounting\Accounts Receivable\~QlikView Reports~\Customer Master\QlikView Customer Master.xlsx]
(ooxml, embedded labels, table is [$(Sheet1)]);
NoCustomer:
Concatenate (CustomerMaster)
Load * Inline [
Customer #,Customer Name (Long),Customer Name (Short),AR Specialist,Combined Name,Combined Name (Credit),Account Type,Customer Type,Domestic,Government,Repayment Terms,Credit Hold,Credit Limit,Sales Code,Parent ID,Credit Code,Inactive,Sales Rep,Combined Name (DSO),Combined Name 2,Repayment Terms 2
-,- NO CUSTOMER/PROJECT -,NO CUSTOMER/PROJECT -,NONE,OTHER,NO CUSTOMER/PROJECT -,Wholesale,NONE,Y,No,0,0,0,0,-,1,No,0,NO CUSTOMER/PROJECT -,OTHER,0
];
//Load Sales
Sales@Low:
LOAD
[Customer #],
[Acct Period],
[Net Sales]
FROM
[\\luckybrand.net\shares\shares\Accounting\Accounts Receivable\~QlikView Reports~\Sales @ Low\Source\Sales @ Low Raw Data.qvd]
(qvd);
//Load AR
ARDetail:
LOAD
*,
(If(Trim([Bucket]) = 'Current' ,1,
If(Trim([Bucket]) = '(1 - 15)' ,2,
If(Trim([Bucket]) = '(16 - 30)' ,3,
If(Trim([Bucket]) = '(31 - 60)' ,4,
If(Trim([Bucket]) = '(61 - 90)' ,5,
If(Trim([Bucket]) = '(91 - 120)' ,6,
If(Trim([Bucket]) = '(121 - 150)' ,7,
If(Trim([Bucket]) = '(151 - 180)' ,8,
9))))))))) as [Bucket Order];
LOAD
*,
ApplyMap('TransTypeOverrideMAP',[Document Number], [Temp - Trans Code 2]) as [Trans Code],
if(Age<=0,'Current',
if(Age>=1 and Age<=15,'(1 - 15)',
if(Age>=16 and Age<=30,'(16 - 30)',
if(Age>=31 and Age<=60,'(31 - 60)',
if(Age>=61 and Age<=90,'(61 - 90)',
if(Age>=91 and Age<=120,'(91 - 120)',
if(Age>=121 and Age<=150,'(121 - 150)',
if(Age>=151 and Age<=180,'(151 - 180)','(180 +)')))))))) as [Bucket];
LOAD
*,
if([CB Code]<>Null() and [Transaction Type]<>'Credit Memo','CB',if([Transaction Type]='Journal' and [Customer #]<>'-','CM',[Temp - Trans Code 1])) as [Temp - Trans Code 2],
[Aging Date]-[Due Date] as [Age],
'AR' as [AR Source],
[Due Date]-[Transaction Date] as [Calculated Repayment Days],
Date(ApplyMap('PeriodConversionMAP',[Due Date]),'MM/DD/YYYY') as [Due Date Period Conversion],
ApplyMap('FiscalMonthMAP',[Due Date]) as [Due Date Fiscal Month],
ApplyMap('FiscalWeekMAP',[Due Date]) as [Due Date Fiscal Week];
LOAD
*,
ApplyMap('TransTypeMAP',[Transaction Type],Null()) as [Temp - Trans Code 1],
DATE([Transaction Date]+ApplyMap('RepaymentDaysCustMAP',[Customer #],ApplyMap('RepaymentDaysMAP',[Temp - Repayment Terms],'0')) ,'MM/DD/YYYY') as [Due Date];
LOAD
*,
Subfield([Temp - Customer # 1],' ',1) as [Customer #],
[Aging Date]-[Transaction Date] as [BBC - Days From Inv Date];
LOAD
Name,
if(WildMatch(Name,'*:*'),subfield(Name,': ',-1),Name) as [Temp - Customer # 1],
[Document Number],
[Transaction Type],
date([Transaction Date],'MM/DD/YYYY') as [Transaction Date],
[Repayment Terms] as [Temp - Repayment Terms],
[CB Code],
[P.O. No.],
[Open Balance],
Date(Date#(LEFT(RIGHT(FileName(),13),8),'YYYYMMDD'),'MM/DD/YYYY') as [Aging Date],
'' as [ADPT],
'' as [C2FO],
'' as [Est. Pmt Date],
'' as [Est. Due Wk],
'' as [Pmt Rec'd Not Applied]
FROM
[\\luckybrand.net\shares\shares\Accounting\Accounts Receivable\~QlikView Reports~\AR Detail\AR Detail*.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [$(Sheet1)]);
//Load Deposits
Temp_Deposits:
LOAD
*,
Subfield([Deposit Temp - Customer # 1],' ',1) as [Customer #],
Debit - Credit as [Balance];
LOAD
if(WildMatch(Name,'*:*'),Subfield(Name,':',-1),Name) as [Deposit Temp - Customer # 1],
Debit,
Credit,
Date(Date#(LEFT(RIGHT(FileName(),13),8),'YYYYMMDD'),'MM/DD/YYYY') as [Deposit Report Date]
FROM
[\\luckybrand.net\shares\shares\Accounting\Accounts Receivable\~QlikView Reports~\AR Detail\Deposits*.xlsx]
(ooxml, embedded labels, header is 5 lines, table is [$(Sheet1)]);
Deposits:
LOAD
[Customer #],
sum(Debit) as [Deposit Debit],
sum(Credit) as [Deposit Credit],
sum([Balance]) as [Deposit Balance],
[Deposit Report Date],
'Deposits' as [Deposit Source]
Resident Temp_Deposits
Group By [Customer #], [Deposit Report Date];
Drop Table Temp_Deposits;
//Load Deals
Temp_Deals:
LOAD
*,
if(WildMatch([Temp - Customer #],'*-*'),Subfield([Temp - Customer #],'-',1),[Temp - Customer #]) as [Customer #],
Date(ApplyMap('PeriodConversionMAP',[Deal Start Date]),'MM/DD/YYYY') as [Deal Period Conversion];
LOAD
[Internal ID] as [Deals Internal ID],
Name as [Deal Name],
Subfield([Deal Company],' ',1) as [Temp - Customer #],
[Deal Status],
[Deal Type],
[Deal Division],
Date([Deal Start Date], 'MM/DD/YYYY') as [Deal Start Date],
Date([Deal End Date], 'MM/DD/YYYY') as [Deal End Date],
num([Deal Number]) as [Deal Number],
[Deal Number Used],
[Deal Amount],
[Deal Amount Used],
[Deal Amount Remaining],
Date(Date#(LEFT(RIGHT(FileName(),13),8),'YYYYMMDD'),'MM/DD/YYYY') as [Deal Report Date]
FROM
[\\luckybrand.net\shares\shares\Accounting\Accounts Receivable\~QlikView Reports~\AR Detail\Deals*.xlsx]
(ooxml, embedded labels, table is [$(Sheet1)]);
Deals:
LOAD
[Customer #],
[Deals Internal ID],
[Deal Name],
[Deal Status],
[Deal Type],
[Deal Division],
[Deal Start Date],
[Deal Period Conversion],
[Deal End Date],
[Deal Number],
[Deal Number Used],
[Deal Amount],
[Deal Amount Used],
[Deal Amount Remaining],
'Deals' as [Deal Source],
[Deal Report Date]
Resident Temp_Deals;
Drop Table Temp_Deals;
//Load Credit Hold & ANS
Temp_CreditHold:
LOAD
*,
if(WildMatch([Temp - Customer #],'*-*'),Subfield([Temp - Customer #],'-',1),[Temp - Customer #]) as [Customer #],
if([Sales Order Start Ship Date]-[Credit Hold Report Date]<=14,'(<=14)',
if([Sales Order Start Ship Date]-[Credit Hold Report Date]>14 and [Sales Order Start Ship Date]-[Credit Hold Report Date]<=28,'(15-28)','(>28)')) as [Sales Order Date Diff],
ApplyMap('PeriodConversionMAP',[Sales Order Start Ship Date]) as [Sales Order Period Conversion];
LOAD
[Internal ID] as [Sales Order Internal ID],
Subfield([Name],' ',1) as [Temp - Customer #],
num([Credit Limit]) as [Credit Limit],
num(Subfield([Bad Debt Amount],' - ',1)) as [Bad Debt Amount],
Date([Sales Order Created Date], 'MM/DD/YYYY') as [Sales Order Created Date],
Date([Sales Order Start Ship Date], 'MM/DD/YYYY') as [Sales Order Start Ship Date],
Date([Sales Order End Date], 'MM/DD/YYYY') as [Sales Order End Date],
Date([Sales Order Ship By Date], 'MM/DD/YYYY') as [Sales Order Ship By Date],
[Sales Order #],
[Customer PO#],
num([Sales Order Amount]) as [Sales Order Original Amount],
Date(Date#(LEFT(RIGHT(FileName(),13),8),'YYYYMMDD'),'MM/DD/YYYY') as [Credit Hold Report Date]
FROM
(ooxml, embedded labels, table is [$(Sheet1)]);
SalesOrders:
LOAD
[Customer #],
[Bad Debt Amount],
Date([Sales Order Created Date],'MM/DD/YYYY') as [Sales Order Created Date],
Date([Sales Order Start Ship Date],'MM/DD/YYYY') as [Sales Order Start Ship Date],
Date([Sales Order End Date],'MM/DD/YYYY') as [Sales Order End Date],
Date([Sales Order Ship By Date],'MM/DD/YYYY') as [Sales Order Ship By Date],
Date([Sales Order Period Conversion],'MM/DD/YYYY') as [Sales Order Period Conversion],
[Sales Order #],
[Customer PO#],
[Sales Order Original Amount],
Date([Credit Hold Report Date],'MM/DD/YYYY') as [Credit Hold Report Date],
'Credit Hold' as [Sales Order Source]
Resident Temp_CreditHold;
Drop Table Temp_CreditHold;
Temp_ANS:
LOAD
*,
if(WildMatch([Temp - Customer #],'*-*'),Subfield([Temp - Customer #],'-',1),[Temp - Customer #]) as [Customer #],
if([Sales Order Start Ship Date]-[ANS Report Date]<=14,'(<=14)',
if([Sales Order Start Ship Date]-[ANS Report Date]>14 and [Sales Order Start Ship Date]-[ANS Report Date]<=28,'(15-28)','(>28)')) as [Sales Order Date Diff],
ApplyMap('PeriodConversionMAP',[Sales Order Start Ship Date]) as [Sales Order Period Conversion];
LOAD
[Internal ID] as [Sales Order Internal ID],
Subfield([Name],' ',1) as [Temp - Customer #],
Date([Sales Order Created Date], 'MM/DD/YYYY') as [Sales Order Created Date],
[Sales Order #],
num([Original Amount]) as [Sales Order Original Amount],
num([Amount Remaining]) as [Sales Order Remaining Amount],
Date([Sales Order Start Ship Date], 'MM/DD/YYYY') as [Sales Order Start Ship Date],
Date([Sales Order End Date], 'MM/DD/YYYY') as [Sales Order End Date],
[Sales Order Memo],
[Sales Order Status],
Date(Date#(LEFT(RIGHT(FileName(),13),8),'YYYYMMDD'),'MM/DD/YYYY') as [ANS Report Date]
FROM
(ooxml, embedded labels, table is [$(Sheet1)]);
ANS:
Outer Join (SalesOrders)
LOAD
[Customer #],
[Sales Order Internal ID],
Date([Sales Order Created Date],'MM/DD/YYYY') as [Sales Order Created Date],
[Sales Order #],
[Sales Order Original Amount],
[Sales Order Remaining Amount],
Date([Sales Order Start Ship Date],'MM/DD/YYYY') as [Sales Order Start Ship Date],
Date([Sales Order Period Conversion],'MM/DD/YYYY') as [Sales Order Period Conversion],
Date([Sales Order End Date],'MM/DD/YYYY') as [Sales Order End Date],
[Sales Order Memo],
[Sales Order Status],
Date([ANS Report Date],'MM/DD/YYYY') as [ANS Report Date],
'ANS' as [Sales Order Source]
Resident Temp_ANS;
Drop Table Temp_ANS;