Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

the measure is not grouped on pivotTable

hi all,

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

the measure is not grouped!

1. output table (Qlikview pivotTable)

table1.png

2. dimension table

%dimensiondes and %dimensionmod:

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

3. Input table

CUSTOMERDIM_ETADIM_SESSO
A11. [1 - 17] anni1. Company
A22. [18 - 25] anniNon Applicabile
A33. [26 - 35] anni3. Maschi
A44. [36 - 45] anni1. Company
A55. [46 - 55] anni1. Company
A66. [56 - 65] anni2. Femmine
A77. [66 - 75] anni2. Femmine
A88. [76 - 85] anni1. Company
A99. [86 >] anni1. Company
A10Non Applicabile2. Femmine

thanks

niko

9 Replies
jerem1234
Valued Contributor II

Re: the measure is not grouped on pivotTable

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!

Not applicable

Re: the measure is not grouped on pivotTable

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).

qlikvw.png

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

sujeetsingh
Honored Contributor III

Re: the measure is not grouped on pivotTable

Niko ,

It is not clear from your explaination .

Can you provide a sample

Not applicable

Re: the measure is not grouped on pivotTable

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
Honored Contributor III

Re: the measure is not grouped on pivotTable

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

Not applicable

Re: Re: the measure is not grouped on pivotTable

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
Valued Contributor II

Re: the measure is not grouped on pivotTable

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?

Not applicable

Re: Re: the measure is not grouped on pivotTable

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%DIMENSIONMODCOUNT(DISTINCT ID_PARTY)note
CLASSE ETA'1. [1 - 17] ANNI10total=100 customer
CLASSE ETA'2. [18 - 25] ANNI25
CLASSE ETA'3. [26 - 35] ANNI25
CLASSE ETA'4. [36+] ANNI40
SESSO1. FEMMINE40total=100 customer
SESSO2. MASCHI60
jerem1234
Valued Contributor II

Re: Re: Re: the measure is not grouped on pivotTable

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!