topic Re: Different selections from the same fact table in QlikView

Different selections from the same fact table

Wed, 05 Feb 2014 09:27:48 GMT

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

Re: Different selections from the same fact table

Anonymous — Wed, 05 Feb 2014 09:33:42 GMT

Kyle

I read this interesting blog post The Key to Heaven by hic yesterday.

I have pasted the below in from it.

Navigation and selections should be left to the user. Don’t automate this. Let the user make the selections and interact with data.

I know this does not answer your question per se, but the blog post is well worth a read and may give you food for thought.

Best Regards, Bill

Re: Different selections from the same fact table

Wed, 05 Feb 2014 10:01:20 GMT

Kyle,

If you are interested in Set Analysis, the following document may interest you : http://community.qlik.com/docs/DOC-4951

Bill is completly right. Using Set Analysis is a good thing but it must empowers the user's selection (through a YTD, a year ago selection ....). If you overwrite user's selection, write a significant title so that the user understands that the graph/table does not show what he has previously selected.

Fabrice

Re: Different selections from the same fact table

Wed, 05 Feb 2014 10:11:44 GMT

Hi Kyle,

Why are you using the aggr() function? Did you try the expression just with sum(Amount) or sum({$<[Status] = {'Active', 'In Progress'}>} Amount) ?

Regards

Re: Different selections from the same fact table

Wed, 05 Feb 2014 11:40:40 GMT

Hi Kyle,

Maeby you can use Alternate States in order to have an easy solution.