topic Measures Groups in QlikView

Measures Groups

Thu, 07 Jun 2012 18:17:57 GMT

Hello, i have a fact table with 6 measures: VTA_MXP, VTA_USD, VTA_UND, BUD_MXP, BUD_USD, BUD_UND.

i would like to group VTA_UND and BUD_UND in a "UNITS GROUP" and the others in a "VALUES GROUP".

It is becouse in the design, i want to filter by "UNITS GROUP" OR "VALUES GROUP" and the charts change to correspondent measures.

Thanks,

Measures Groups

Thu, 07 Jun 2012 18:36:10 GMT

Hi,

In this case you can using IF condition:

if((FieldName = 'VTA_UND') or (FieldName = 'BUD_UND') ),'UNITS GROUP', ’VALUES GROUP‘）

You can using this script in dimension or when you load the table.

Hope this can help you.

Regards

Measures Groups

regowins — Thu, 07 Jun 2012 19:53:48 GMT

I think Heri's answer is correct, but as an alternative maybe you can create flags in the script for Units Group and Values Group.

Measures Groups

Thu, 07 Jun 2012 20:04:10 GMT

Hello Heri,

Thanks, but i didn´t understand where i should use the condition, it is like a variable or field?

The Fact is:

RIGHT KEEP(PRODUCTOS)
RIGHT KEEP(FUERZA_VTAS)
FACT:
LOAD MakeDate(Left(Text("TIPC_ID_PERI"),4), Mid(Text("TIPC_ID_PERI"),5,2),Mid(Text("TIPC_ID_PERI"),7,2)) AS FCH,
"PRES_ID_PRES" AS "ID_PRES",
"CIUD_ID_CIUD"&"REPR_ID_REPR" AS "ID_FVTAS",

VTA_UND,

BUD_UND,

VTA_MXP,

VTA_USD,

BUD_MXP,

BUD_USD;

SQL

SELECT *
FROM "DM_AUDITORIAS"."INAU_RESU"
ORDER BY TIPC_ID_PERI ASC;

Measures Groups

Thu, 07 Jun 2012 20:24:28 GMT

Hi, if your table like that, you must load your table with crosstable. Try this:

CrossTable(Type, Value, 3)

FACT:
LOAD MakeDate(Left(Text("TIPC_ID_PERI"),4), Mid(Text("TIPC_ID_PERI"),5,2),Mid(Text("TIPC_ID_PERI"),7,2)) AS FCH,
"PRES_ID_PRES" AS "ID_PRES",
"CIUD_ID_CIUD"&"REPR_ID_REPR" AS "ID_FVTAS",

VTA_UND,

BUD_UND,

VTA_MXP,

VTA_USD,

BUD_MXP,

BUD_USD;

SQL

SELECT *
FROM "DM_AUDITORIAS"."INAU_RESU"
ORDER BY TIPC_ID_PERI ASC;

and in your chart dimension, adding calculated dimension using :

if((Type= 'VTA_UND') or (Type= 'BUD_UND') ),'UNITS GROUP', ’VALUES GROUP‘） // 'Type' is a field name that contain grouped Field -- VTA_MXP, VTA_USD, VTA_UND, BUD_MXP, BUD_USD, BUD_UND)

then in expression you can direct using your calculation. For ex: sum(Value) // 'Value' is the value of each measurment.

Regards

Measures Groups

Thu, 07 Jun 2012 22:40:46 GMT

i got this error:

Illegal combination of prefixes
RIGHT KEEP(PRODUCTOS)
RIGHT KEEP(FUERZA_VTAS)
CrossTable(Type, Value, 3)
FACT:
LOAD MakeDate(Left(Text("TIPC_ID_PERI"),4), Mid(Text("TIPC_ID_PERI"),5,2),Mid(Text("TIPC_ID_PERI"),7,2)) AS FCH,
"PRES_ID_PRES" AS "ID_PRES",

"CIUD_ID_CIUD"&"REPR_ID_REPR" AS "ID_FVTAS",

VTA_UND,

BUD_UND,

VTA_MXP,

VTA_USD,

BUD_MXP,

BUD_USD;

SQL

SELECT *
FROM "DM_AUDITORIAS"."INAU_RESU"
ORDER BY TIPC_ID_PERI ASC;

Re: Measures Groups

Thu, 14 Jun 2012 16:03:44 GMT

i created a list box with 'Units' and 'Values'

and i added a condicional function (IF) in the charts

Thanks

Re: Measures Groups

Thu, 14 Jun 2012 16:32:06 GMT

Hi,

try to comment this lines:

RIGHT KEEP(PRODUCTOS)

RIGHT KEEP(FUERZA_VTAS)

thanks.

Regards,

Heri