Is this possible through Set Analysis?

[Anonymous]

Hi everyone

I have the following data model where I have a list of Products sold and a field where it is indicated if a product was sold as result of a devolution from other:

Product	Returned Product	#Packages
Product A		1
Product A	Product B	2
Product B		5
Product B		3
Product C	Product A	7

I want to achieve the following result:

Product	Sales	Returns
Product A	3	7
Product B	8	2
Product C	7	0

Is this feasible through set analysis?

Many Thanks for your support.

[aarkay29]

May be this

Return=sum({1<[Returned Product]=p(Product)>}#Packages)

[Anonymous]

Hi Aar,

Thanks for your reply but that way results on a a new line, Product A, with 2 returns.

The objective is to have only the line with Product B.

[sunny_talwar]

May be like this

Dimension

Product

Expressions

=Sum({<[Returned Product] = {"=Len(Trim([Returned Product])) = 0"}>}#Packages)

=Sum(TOTAL {1<[Returned Product] = p(Product)>} #Packages)

[Anonymous]

Hi Sunny,

Thanks for your reply. The issue here is when we have more that one Product with returns like I have put on your attached qvw. In this situations I will add all returned packages in all Products lines.

Please check the attached qvw

Many Thanks

[sunny_talwar]

But in your initial post you mentioned this:

"I want to achieve the following analysis when the user selects Product B"

I assumed that were going to be making a single selection in Product before you will see this. Is that not true anymore?

[Anonymous]

That was just an example of the expected behaviour when the user selects Product B. But the user can perform any selection.

I will edit the initial post  and present the expected result wihout selections. Sorry if I was not clear

[sunny_talwar]

Don't have to be sorry my friend. Is modifying script a problem? If not, then try like this

Table:

LOAD *,

  If(Len(Trim([Returned Product])) = 0, Product, [Returned Product]) as DimProduct;

LOAD * INLINE [

    Product, Returned Product, #Packages

    Product A, Product B, 2

    Product A, , 1

    Product B, , 5

    Product B, , 3

    Product C, Product A, 7

];

Now use DimProduct as your dimension and two expressions

1) =Sum({<[Returned Product] = {"=Len(Trim([Returned Product])) = 0"}>}#Packages)

2) =Sum({<[Returned Product] = {"=Len(Trim([Returned Product])) > 0"}>}#Packages)

[Anonymous]

But in that way we loose the sales of Product A where the Product B was returned (2 Packages) because the DimProduct on that record become Product B.

Check the first post for the desire result.

Many Thanks once again

[sunny_talwar · Accepted solution]

Check out the link table approach here

Table:

LOAD RowNo() as RowNum,

  *;

LOAD * INLINE [

    Product, Returned Product, #Packages

    Product A, Product B, 2

    Product A, , 1

    Product B, , 5

    Product B, , 3

    Product C, Product A, 7

];

LinkTable:

LOAD DISTINCT RowNum,

  Product as DimProduct,

  'Sales' as Flag

Resident Table

Where Len(Trim(Product)) > 0;

Concatenate (LinkTable)

LOAD DISTINCT RowNum,

  [Returned Product] as DimProduct,

  'Return' as Flag

Resident Table

Where Len(Trim([Returned Product])) > 0;

Dimension

DimProduct

Expressions

=Sum({<Flag = {'Sales'}>}#Packages)

=Sum({<Flag = {'Return'}>}#Packages)