Hi,
I have an orders table for which i want to create 4 straight or pivot tables each using a different subset of the data. Currently i have 4 variations of the table, 1 for each chart which even as a novice QlikView user i know is not good. I think this can be achieved using set analysis but i've never done anything with this.
What i have so far:
2 variables for my date limits:
let vaCutoffDate = date(div(monthend( AddMonths( monthend ( today(0) ), 2 ) ),1),'YYYY-MM-DD');
let vaRenewStart = date( MonthStart(today(0), 0),'YYYY-MM-DD');
4 tabs, each with the same table but different selection criteria on the SQL query and each table populates a QlikView straight or pivot table.
Orders:
LOAD Status,
Summary,
"Start Date" as ShipDate,
"Duration (mths)",
"Renewal Date" as RenewDate,
"Renewal Date" as CalRenewDate,
"Invoice Date",
"Paid Date",
"Cancellation Date",
"Contract Number",
"Order Number",
"Invoice Number",
"Customer Number",
"Total Amount",
"Total Cost",
"Total Margin",
"Acct Mgr Id",
"Acct Mgr Name",
"Commission %",
"Total Commission",
"VAT%",
Comments as OrderComments,
"Payment Terms",
"Purchase Type",
CustomId as OrderKey,
ClientId as CompanyKey;
SQL SELECT *
FROM SYN_Orders_Report
where ( [Invoice Number] is null or [Invoice Number] = '' ) and
( Status = 'Active' or Status = 'In Progress' ) and
not [Purchase Type] in ('New System', 'Increase') and
"Start Date" <= '$(vaCutoffDate)';
Tables 2 SQL selection:
SQL SELECT *
FROM SYN_Orders_Report
where Status = 'Cancelled';
Table 3 SQL:
SQL SELECT *
FROM SYN_Orders_Report
where Status = 'In progress' and
not [Purchase Type] in ('New System', 'Increase') and
"Start Date" <= '$(vaCutoffDate)';
Table 4 SQL:
SQL SELECT *
FROM SYN_Orders_Report
where ( [Invoice Number] is null or [Invoice Number] = '' ) and
Status = 'In Progress' and
not [Purchase Type] in ('New System', 'Increase');
The first chart uses table #1 and shows the total Renewal amount & Total cost for each order as:
=aggr(sum(Amount), OrderID)
so i changed this to:
=aggr(sum({$<[Status] = {'Active', 'In Progress'}>} Amount), OrderID)
to add one of the criteria from the SQL but it doesn't appear to have changed the data in the chart.
I'll try to create an example qvw and post it later but in the meantime if anyone could give me some pointers on how i can achieve what i need i'd be most grateful.
Thanks,
Kyle
