Matrix Creation and usage

[#1]

Hi, I am new to Qlikview and wanted to assistance to solve a particular issue.

Essentially trying to create a dynamic table basis averages of a criteria in a particular city. The value of the same can then be used for analysis similar to vlookup in excel.

The table based on the dynamic data will be something like below. The list of cities and criteria would vary as per the database and therefore dynamic in nature.

	Criteria
City		1	2	3	4	5
	A	21	78	758	53	123
	B	123	56	789	745	45
	C	123	142	786	453	786
	D	896	546	7896	254	78
	E	789	7853	8563	453	56
	F	78	4253	78	896	786

eg The average value of criteria 1 in City A is 21 etc.

Now I take a particular row from the database and compare it against the table. Eg.Say criteria 1 for city A in the database is 32 and comparing it against the average 21 we can infer that the value is greater than the average.

Thereafter all those unique ID's where the amount is greater than say 25% of the averages from the table are displayed.

Please assist as to how to build the same.

Thanks in advance

[jonathandienst]

Do you want to do the filtering in the front end, or in the load script?

Is the list of criterion averages in a spreadsheet? Is it user supplied?

Or is it calculated? What does the source data look like?

>>here the amount is greater than say 25% of the averages

This a little confusing and rather vague. Please be more specific about how you want to filter the data.

>>Thereafter all those unique ID's ... from the table are displayed.

Displayed how? In a list box, table, chart...

[#3]

Ok... say we have a database of city and product Name and the Sales price. Therefore in the excel the data will be of the format (in the header) as Sales ID , Product Name, City and Sales Price. The matrix has to be created as mentioned above and the vale taken is Average of Sales Price. This has to be done in the load script.

Thereafter we compare a particular Sales ID against a product name and City and compare it against the average sales price. Therefore if the sales price of that sales ID is greater than 25% of the average sales price (of that particular Product Name and City) then we extract that into a list box.

Trust this clarifies..

Thanks again

[Anonymous]

Hi,

I've created a sample app.

Please see attached.

I've used 5% to test due to not have movements > 25%.

Change the variable in the load script to 0.25.

Hope it helps...

[#5 · Accepted solution]

Dear Deran,

Thanks a lot for the support.

Managed to get the output as per the directions provided.

Thanks again.