Alternate State Issue

[Anonymous]

Hi,

Need your help.

I have the following table:

Karthick	Muthu	CAR	100
Veni	Thangam	TV	200
Sudheep	Suthan	TOY	300
Anand	Muthu	SHOE	400
Suriya	Thangam	CAR	500
Ganesh	Suthan	TV	600
Karthick	Muthu	TOY	700
Veni	Thangam	SHOE	800
Sudheep	Suthan	CAR	900
Anand	Muthu	TV	1000
Suriya	Thangam	TOY	1100
Ganesh	Suthan	SHOE	1200

Also, i have the following expression:

=sum( {$<Customer = P({<Product={'CAR'}>}Supplier)>}Sales). But I am getting a value of zero for this. Why I am getting zero for this?

My expectation is 7800 (because CAR is associated with Three Suppliers (Muthu,Thangam and Suthan and those suppliers are associated to all the Customers)

Thanks,

Karthick S

[Anonymous]

Hi Prasenna,

Your expression will definitely work. but i have taken that example from the qlikview reference manual.

[sunny_talwar]

The sample you have provided doesn't have headers, can you add that. Also, would you be able to provide the expected output based on the sample you have provided?

[Anonymous]

In the reference manual under the header:

"Set Modifiers with Implicit Field Value Definitions", there are few examples. Here it is:

1.     sum( {$<Customer = P({1<Product={'Shoe'}>} Customer)>}

Sales )

returns the sales for current selection, but only those customers

that ever have bought the product ‘Shoe’. The element

function P( ) here returns a list of possible customers;

those that are implied by the selection ‘Shoe’ in the field

Product.

2.     sum( {$<Customer = P({1<Product={'Shoe'}>})>} Sales )

same as above. If the field in the element function is omitted,

the function will return the possible values of the field

specified in the outer assignment.

3.     sum( {$<Customer = P({1<Product={'Shoe'}>} Supplier)>}

Sales )

returns the sales for current selection, but only those customers

that ever have supplied the product ‘Shoe’. The ele372

ment function P( ) here returns a list of possible suppliers;

those that are implied by the selection ‘Shoe’ in the field

Product. The list of suppliers is then used as a selection in

the field Customer.

I tried the third expression with my own inputs. My expectation is 7800 (because CAR is associated with Three Suppliers (Muthu,Thangam and Suthan and those suppliers are associated to all the Customers)

[Anonymous]

Hi Sunny,

Did you get a chance to look at this?

Thanks

[sunny_talwar]

Can you share the link from the reference manual?

[Anonymous]

Here is the Link:

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/SetAnalysis/set-m...

Please look at the third example.

[sunny_talwar]

Try this expression:

=Sum({$<Customer = P({<Supplier = P({<Product={'CAR'}>}Supplier)>} Customer)>}Sales)

[Anonymous]

Thanks Sunny, this works. So this means we cannot use two different dimensions in the P() meaning we can use only dim1=p(dim1) and not dim1=p(dim2).

Once again, thank you so much for taking your time and trying this out for me.

[sunny_talwar]

I think the only time you can use is if you want to pick possible values from dim2 for dim1. If none of the values match, then you will get 0. For example, if possible values of dim2 = a, b, c.and dim1 has these values = a, f, g. The dim1 = p(dim2) will include dim1 = a in the expression. But if dim1 = K, L, M, then dim1 = p(dim2) will include nothing because none of the possible values from dim2 are not available in dim1

[Anonymous]

Hi Karthick, the Sunny expression is the same than mine, more earlier than sunny response.

Then, the reference manual explanins that your expression "returns the sales for current selection, but only those customers

that ever have supplied the product ‘Shoe’".

This is correct because the customers and the suppliers have the same name, but if the names are different the expression will return 0.

Regards!