Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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.
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;
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
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;
i created a list box with 'Units' and 'Values'
and i added a condicional function (IF) in the charts
Thanks
Hi,
try to comment this lines:
RIGHT KEEP(PRODUCTOS)
RIGHT KEEP(FUERZA_VTAS)
thanks.
Regards,
Heri