topic Re: Alternate State Issue in QlikView

Alternate State Issue

Anonymous — Tue, 30 Aug 2016 02:45:16 GMT

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

Re: Alternate State Issue

mayankraoka — Tue, 30 Aug 2016 07:52:30 GMT

Can you share the sample app?

Regards,

Mayank

Re: Alternate State Issue

mayankraoka — Tue, 30 Aug 2016 07:55:47 GMT

Try this one.

sum( {$<Customer = P({<Product={'CAR'}>}Customer)>}Sales).

Re: Alternate State Issue

antoniotiman — Tue, 30 Aug 2016 08:06:28 GMT

Hi,

try

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

Re: Alternate State Issue

Anonymous — Tue, 30 Aug 2016 08:25:45 GMT

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?

You are comparing Customer with Supplier, how this will work??

Re: Alternate State Issue

sunny_talwar — Tue, 30 Aug 2016 11:07:34 GMT

I would just do like this:

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

Re: Alternate State Issue

Anonymous — Tue, 30 Aug 2016 11:26:00 GMT

Hi Karthick:

Try this expression

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

It calculates the suppliers that have sold CAR product, and the customers that have purchased to the Suppliers who have sold a car.

Regards!

Re: Alternate State Issue

Anonymous — Tue, 30 Aug 2016 13:38:49 GMT

Hi karthick01

Please try this.

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

Re: Alternate State Issue

Anonymous — Wed, 31 Aug 2016 00:07:28 GMT

Hi Balraj,

I have taken the below expression from Qlikview reference manual:

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

Sales ) and the explanation is:

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 am trying to execute the same thing. How does this work then?

Re: Alternate State Issue

Anonymous — Wed, 31 Aug 2016 00:08:10 GMT

Sunny,

I have taken the below expression from Qlikview reference manual:

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

Sales ) and the explanation is:

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 am trying to execute the same thing and thats why I am not understanding why I am getting 0.

Re: Alternate State Issue

Anonymous — Wed, 31 Aug 2016 00:09:31 GMT

Hi Prasenna,

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

Re: Alternate State Issue

sunny_talwar — Wed, 31 Aug 2016 00:11:00 GMT

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?

Re: Alternate State Issue

Anonymous — Wed, 31 Aug 2016 00:28:27 GMT

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)

Re: Alternate State Issue

Anonymous — Thu, 01 Sep 2016 00:17:23 GMT

Hi Sunny,

Did you get a chance to look at this?

Thanks

Re: Alternate State Issue

sunny_talwar — Thu, 01 Sep 2016 00:20:42 GMT

Can you share the link from the reference manual?

Re: Alternate State Issue

Anonymous — Thu, 01 Sep 2016 00:49:05 GMT

Here is the Link:

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/SetAnalysis/set-modifiers-implicit-fi…

Please look at the third example.

Re: Alternate State Issue

sunny_talwar — Thu, 01 Sep 2016 01:07:24 GMT

Try this expression:

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

Re: Alternate State Issue

Anonymous — Thu, 01 Sep 2016 02:45:37 GMT

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.

Re: Alternate State Issue

sunny_talwar — Thu, 01 Sep 2016 02:49:26 GMT

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

Re: Alternate State Issue

Anonymous — Thu, 01 Sep 2016 06:12:50 GMT

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!