the measure is not grouped on pivotTable

[#1]

hi all,

i have a problem with following table/dimension/measure:

the measure is not grouped!

1. output table (Qlikview pivotTable)

2. dimension table

%dimensiondes and %dimensionmod:

%DIMENSION	%DIMENSIONMODDES	%DIMENSIONMOD	%DIMENSIONMODTYPE	%DIMENSIONMODORDER
DIM_ETA	Classe Età	1. [1 - 17] anni		1
DIM_ETA	Classe Età	2. [18 - 25] anni		1
DIM_ETA	Classe Età	3. [26 - 35] anni		1
DIM_ETA	Classe Età	4. [36 - 45] anni		1
DIM_ETA	Classe Età	5. [46 - 55] anni		1
DIM_ETA	Classe Età	6. [56 - 65] anni		1
DIM_ETA	Classe Età	7. [66 - 75] anni		1
DIM_ETA	Classe Età	8. [76 - 85] anni		1
DIM_ETA	Classe Età	9. [86 >] anni		1
DIM_ETA	Classe Età	Non Applicabile		1
DIM_SESSO	Sesso	1. Company		1
DIM_SESSO	Sesso	2. Femmine		1
DIM_SESSO	Sesso	3. Maschi		1
DIM_SESSO	Sesso	N.A.		1

3. Input table

CUSTOMER	DIM_ETA	DIM_SESSO
A1	1. [1 - 17] anni	1. Company
A2	2. [18 - 25] anni	Non Applicabile
A3	3. [26 - 35] anni	3. Maschi
A4	4. [36 - 45] anni	1. Company
A5	5. [46 - 55] anni	1. Company
A6	6. [56 - 65] anni	2. Femmine
A7	7. [66 - 75] anni	2. Femmine
A8	8. [76 - 85] anni	1. Company
A9	9. [86 >] anni	1. Company
A10	Non Applicabile	2. Femmine

thanks

niko

[jerem1234]

My guess is that there is some kind of linking issue between your fields or your data model, but thats just a guess. How does KPI_ETA fit into your data? If you could post what your data model looks like or even better a sample qvw demonstrating your problem, you might be able to get help further.

Hope this helps!

[#3]

hi,

this is my data model.

into the output table (1. output table) must be avg(kpi_eta) if %dimension='DIM_ETA' else count(distinct id_party).

p.s.: I am looking for, if it is possible to produce an example QLW

[sujeetsingh]

Niko ,

It is not clear from your explaination .

Can you provide a sample

[#5]

in attached:

1. sample_data_niko.zip ==> unzip data in C:\TEMP

2. SAMPLE_NIKO.qvw ==> script with import data from C:\TEMP

--WE HAVE MODIFIED THE MEASURE UPCASE (Modello commerciale, Brand, Market)--

thanks niko

[sujeetsingh]

Your data model is really lacking the connectivity.

Table qlProfcus_dim_mod   do not have any key linking with QLProfcus_dim_modello

So what i concluded is that you need to kave a linking of  qlProfcus_dim and qlProfcus_dim_mod 

to be linked with  QLProfcus_fact.

So go with finding some key relation to link

[#7]

sujeetsingh wrote:

Your data model is really lacking the connectivity.

The model is simple: FACT+ANAG (Customer)+MODELLO (Product)+MARKET

Table qlProfcus_dim_mod  do not have any key linking with QLProfcus_dim_modello

qlProfcus_dim, qlProfcus_dim_mod e qlProfcus_kpi must have no connection with the star-schema because they are "dimensions name" metadata

So what i concluded is that you need to kave a linking of  qlProfcus_dim and qlProfcus_dim_mod 

to be linked with  QLProfcus_fact.

qlProfcus_dim and qlProfcus_dim_mod are linked because the first-one is list of "dimension name" and second-one list of "dimension name"+"dimension values"

So go with finding some key relation to link

[jerem1234]

I would have to agree with sujeetsingh that there needs some connection between the dimensions you are using and the values you are using in your expression. Else there is no way for Qlikview to know how to group by the dimensions you have, with the correct value when there are located in two isolated tables.

Also, you want to try to avoid synthetic keys. To do this, you can combine the two fields that the tables have in common like:

COD_MARKET&COD_MODELLO as Key

and then change the names for COD_MARKET and COD_MODELLO in each table so that they don't match and are linked only by the field Key.

Could you restate your problem? Im guessing it's the numbers you are getting for your expressions. This is caused by the lack of a link between your dimensions and expressions. What do you want your output to look like?

[#9]

jerem1234 wrote:

I would have to agree with sujeetsingh that there needs some connection between the dimensions you are using and the values you are using in your expression. Else there is no way for Qlikview to know how to group by the dimensions you have, with the correct value when there are located in two isolated tables.

I agree with you! but if i want to use a dynamics parameter (e.g. KPI or DIMENSION)  %metrics or %dimension I should use these tables that logically should only drive the output and must not be connected with the star-schema data.

e.g.: http://youtu.be/QSJhYdJHHjA

Also, you want to try to avoid synthetic keys. To do this, you can combine the two fields that the tables have in common like:

COD_MARKET&COD_MODELLO as Key

and then change the names for COD_MARKET and COD_MODELLO in each table so that they don't match and are linked only by the field Key.

Good Idea! thanks - I will provide to concatenate the super primaryKey these key fields

Could you restate your problem? Im guessing it's the numbers you are getting for your expressions. This is caused by the lack of a link between your dimensions and expressions. What do you want your output to look like?

see the following example

TO BE output table in qlikview

%DIMENSION	%DIMENSIONMOD	COUNT(DISTINCT ID_PARTY)	note
CLASSE ETA'	1. [1 - 17] ANNI	10	total=100 customer
CLASSE ETA'	2. [18 - 25] ANNI	25
CLASSE ETA'	3. [26 - 35] ANNI	25
CLASSE ETA'	4. [36+] ANNI	40
SESSO	1. FEMMINE	40	total=100 customer
SESSO	2. MASCHI	60

[jerem1234]

So you want dynamic expressions and dimensions? Then please find attached an example that resembles the one in the video you posted. If you want dynamic dimensions, you would do the same like you have, BUT the thing is, you still need dimensions to go off of in your data. You can have dimensions CLASSE ETA', SESSO, etc in separate table for dynamic dimensions, but they need to be relevant to your data. What dimension does Classe ETA' refer to? What dimension does Sesso refer to? I don't see how you are coming up with those numbers in your table.

Hope this helps!