HELP WITH SET ANALYSIS

[informatica_grupobarea]

Dear experts,

I am trying to filter data with sum function in expression and somehow it is not working. I have searched the same topics and tried to implement solutions which were suggested before but no luck.

I have this data:

TABLE1

ITEM	YEAR	NAME	SALES
A	2019	JOHN	5
B	2019	LUCAS	10
C	2019	ANDY	20
A	2019	LUCAS	10
B	2019	ANDY	10

TABLE2

ITEM	YEAR	COUNTRY
A	2019	SPAIN
B	2019	SPAIN
D	2019	SPAIN
E	2019	ITALY

I need to make a pivot table that shows all the items from Spain and the amount of sales in 2019 even if the sales were zero:

NAME	ITEM	SALES	TOTAL SALES 2019 BY NAME (ALL ITEMS)
JOHN	A	5	5
	B	0	5
	D	0	5
LUCAS	A	10	20
	B	10	20
	D	0	20
ANDY	A	0	30
	B	10	30
	D	0	30

¿Can you help me to archive this?

Thanks in advance

[Vegar]

try using a sum total expression. 

sum(total <NAME> SALES)

[informatica_grupobarea]

Thanks for your help Vegar.

But he problem is other

I don't know how to filter the item dimension with all the SPAIN items even if they had not sales in 2019 and not show items from other countries even it they had sales in 2019.

In the TOTAL SALES column I want to sum all the sales gorup by NAME even if the ITEM belongs to other COUNTRY.

Now I'm having this result that isn't the desired result:

The expressions are:

Expression 1: Sum({<COUNTRY={"SPAIN"}>}SALES)

Expression 2: Sum(Total <NAME>SALES)

I've tried also:

Expression 1:  Sum({<COUNTRY={"SPAIN"}>}SALES)

Expression 2: Sum(Total <NAME>{<COUNTRY={"SPAIN"}>}SALES)

But the result isn't the expected too:

This is the INLINE tables that I've used:

Table1:
LOAD * INLINE [
ITEM,YEAR,NAME,SALES
A,2019,JOHN,5
B,2019,LUCAS,10
C,2019,ANDY,20
A,2019,LUCAS,10
B,2019,ANDY,10
];

Table2:
LOAD * INLINE [
ITEM,YEAR,COUNTRY
A,2019,SPAIN
B,2019,SPAIN
D,2019,SPAIN
E,2019,ITALY
];

[Vegar]

You don't have all the dimensions available for all users. You might need to add these to your transactions. 

In my script below I have generated a empty transaction for all combinations, but you only need for those that are not present.

Table1:
LOAD * INLINE [
ITEM,YEAR,NAME,SALES
A,2019,JOHN,5
B,2019,LUCAS,10
C,2019,ANDY,20
A,2019,LUCAS,10
B,2019,ANDY,10
];

Table2:
LOAD * INLINE [
ITEM,YEAR,COUNTRY
A,2019,SPAIN
B,2019,SPAIN
D,2019,SPAIN
E,2019,ITALY
];

for each _item in FieldValueList('ITEM')
for each _year in FieldValueList('YEAR')
for each _name in FieldValueList('NAME')
  Concatenate (Table1)
  LOAD *,
    null() as SALES //You could also use 0 as SALES
  Inline [
  ITEM,YEAR,NAME
  $(_item),$(_year),$(_name)
];
next _name
next _year
next _item

This might not be the prettiest of solutions and it might not be applicable on all data sets,  but it will get you the desired vizualisation. 

If you want it to calculate only spain then add a spain modifier to to your sum sales as you did in your samples in you earlier posting.

[informatica_grupobarea]

Hey Vegar, maybe is not the prettiest of solutions but it works 😉

I've thinked that with some parameter of set analysis (maybe the p or e modifiers) I would be able to reach the goal but I couldn't.

Your solution is a kind of cross join isn't it.

Anyway I have a problem yet and is how to add the total column including the sales amount for all items but not showing the C value in ITEM dimmension.

When I include this expression, the result is not the expected:

sum(TOTAL <NAME> SALES)

If I try to add the set analysis also to the expression the total is not correct:

sum(TOTAL <NAME>{<COUNTRY={"SPAIN"}>} SALES)