Developing detailed trade statistics table

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I want to create a detailed table of trade statistics in Qlikview, and I would like some advice on how best to architect this table.

I have two tables comprising all items that a country has imported and exported for the last four years. My task is to create a table with totals of weights, tax and duties paid for different sets of items over different timeframes and at different locations. e.g. Vehicles, Petroleum Products, Consumer goods etc.

I can filter my data using a number of methods, the most straightforward by using set analysis. Examples below:

Collected Export Tax: Sum({$<TAR_PRC_NAT+={000},TAR_PRC_EXT*={1000}>})

Electrical Products: =Sum({$<TAR_HSC_NB1+={'84*'},TAR_HSC_NB1+={'85*'},TAR_HSC_NB1-={'8429*'}>})

I first tried using these as a calculated dimension (by embedding these sets within an aggr function) and then using expressions to make sum(field name) calculations for each relevant field. I get the right numbers, but I can't work out how to link and correctly display the expression as related to each dimension in the table layout.

The second option I tried was adding the dimensions I require to a simple table and then hiding the columns. I then write an expression that sum's up the values in the set that I require and displays the total. This again works fine and I get the current numbers, but has a negative side effect of also displaying all values that made up the total, which occupies numerous additional rows. I can hide then using the dimensionality function or changing font colour, but the rows themselves still appear (blanked out) and make the resultant display very messy.

I've scoured the web and have been unable to find many relevant examples of qlikview being used to generate tables of filtered/ calculated summary data.

Can someone point me in the right direction here so that I can have a look at some examples where people have done this in the past?

Another option would be for me to perhaps categorise data into sets during the load. If the experts on this forum think that might be a good approach, are you able to provide me with some examples how to create additional fields in a qvd based on the results ofset analysis performed during a load?

I've only been using Qlikview for a week, so any still learning the basics. Any help would be much appreciated.

Thanks and regards,

Mike

Re: Developing detailed trade statistics table

Fri, 15 Jul 2016 08:33:44 GMT

Here's some further clarification in relation to my question above.

Illustration of problem

Sample Raw Data

IDE_AST_DAT	TAR_HSC_NB1	TAR_PRC_NAT	TAR_PRC_EXT	VIT_WGT_GRS	TAX_AMT
1/1/2016	27108573	000	1000	1000	1493848
1/1/2016	27118567	450	4500	1234	4948593
2/1/2016	87010543	500	4000	4542	0
4/1/2016	84039583	100	4000	4531	994582
4/1/2016	8574921	000	1000	10	4593025

IDE_AST_DAT = Assessment Date

TAR_HSC_NB1 = Goods Tariff Code

TAR_PRC_NAT = National Procedure Code

TAR_PRC_EXT = Extended Procedure Code

VIT_WGT_GRS = Gross Mass

TAX_AMT = Tax and Duties

Sample Display Required (incomplete)

Main Product Groups	Gross Mass	Tax and Duties
Exports	Sum of Gross Mass for goods in Export Set	Sum of Tax and Duties for goods in Exports Set
Electrical	Sum of Gross Mass for goods in Electrical Set	Sum of Tax and Duties for goods in Electrical Set
Petroleum Products	Sum of Gross Mass for goods in Petroleum Set	Sum of Tax and Duties for goods in Petroleum Products Set

Standard Qlikview Date Filter

IDE_AST_DAT is linked to master calendar.

Set statements for Main Product Groups (Can also be configured using match/ wildmatch for same result)

Exports - Sum({$<TAR_PRC_NAT={000},TAR_PRC_EXT*={1000}>})

Electrical - Sum({$<TAR_HSC_NB1*={'84*'},TAR_HSC_NB1+={'85*'},TAR_HSC_NB1-={8429}>})

Petroleum Products - Sum({$<TAR_HSC_NB1+={'2710*'},TAR_HSC_NB1+={'2711*'}>})

topic Re: Developing detailed trade statistics table in QlikView

Developing detailed trade statistics table

Re: Developing detailed trade statistics table