Measures Groups

[#1]

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,

[#2]

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

[regowins]

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.

[#4]

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;

[#5]

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

[#6]

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;

[#7]

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

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

Thanks

[#8]

Hi,

try to comment this lines:

RIGHT KEEP(PRODUCTOS)

RIGHT KEEP(FUERZA_VTAS)

thanks.

Regards,

Heri