topic Re: Is this possible through Set Analysis? in QlikView

Is this possible through Set Analysis?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Is this possible through Set Analysis?

aarkay29 — Tue, 20 Jun 2017 14:58:01 GMT

May be this

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

Re: Is this possible through Set Analysis?

Anonymous — Tue, 20 Jun 2017 15:18:07 GMT

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.

Re: Is this possible through Set Analysis?

sunny_talwar — Tue, 20 Jun 2017 15:22:54 GMT

May be like this

Dimension

Product

Expressions

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

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

Re: Is this possible through Set Analysis?

Anonymous — Tue, 20 Jun 2017 16:30:41 GMT

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

Re: Is this possible through Set Analysis?

sunny_talwar — Tue, 20 Jun 2017 16:51:53 GMT

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?

Re: Is this possible through Set Analysis?

Anonymous — Tue, 20 Jun 2017 16:58:10 GMT

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

Re: Is this possible through Set Analysis?

sunny_talwar — Tue, 20 Jun 2017 17:01:46 GMT

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)

Re: Is this possible through Set Analysis?

Anonymous — Tue, 20 Jun 2017 17:25:33 GMT

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

Re: Is this possible through Set Analysis?

sunny_talwar — Wed, 21 Jun 2017 02:26:00 GMT

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)

Re: Is this possible through Set Analysis?

Anonymous — Fri, 30 Jun 2017 09:45:29 GMT

Thanks,

With this aproach I was able to get the result.