topic Re: Conditional calculation NOT based on dimension in a table in QlikView

Conditional calculation NOT based on dimension in a table

Wed, 16 Jul 2014 10:41:28 GMT

Hi All

I have a requirement to do a particular calculation as follows :

Source data looks like --

Catalog#	NTA	Customer	Price
ABC123	A	x	12.32
ABC123	A	x	2.4
ABC123	A	y	6.5
ABC123	B	y	8.6
ABC123	B	z	9.2
ABC123	B	z	8.9

I have selections on Catalog# and NTA. I have a button to Show/Hide Customer from the table.

I have to display min Price based on Catalog#, NTA and Customer against Catalog#

So the output I should get

ABC123 $2.4

irrespective of the 'Show/Hide' Customers.

Please advise. Attached is the sample file.

Re: Conditional calculation based on columns displayed

alexandros17 — Wed, 16 Jul 2014 10:45:06 GMT

Here is your example:

Re: Conditional calculation based on columns displayed

Wed, 16 Jul 2014 10:54:42 GMT

Thanks Alessandro but I cant see any difference in the file you provided.

I need the same value , i.e $2.4 against ABC123 even if the customer data is shown.

Re: Conditional calculation NOT based on dimension in a table

Wed, 16 Jul 2014 11:52:09 GMT

Forum experts pls help. Can Set Analysis be used in this case?

Re: Conditional calculation NOT based on dimension in a table

Anonymous — Wed, 16 Jul 2014 12:09:33 GMT

In your Straight Table object how about :

Tab: Dimension Limits
Highlight: Customer :
- Show only: Smallest: 1

Re: Re: Conditional calculation based on columns displayed

alexandros17 — Wed, 16 Jul 2014 12:17:54 GMT

Ok I did not understood what you needed, try now with the example

Re: Conditional calculation NOT based on dimension in a table

male_carrasco — Wed, 16 Jul 2014 12:22:24 GMT

Please try :

aggr(min(Price), Catalog#)

Best Regards.

Ale.-

Re: Conditional calculation NOT based on dimension in a table

Wed, 16 Jul 2014 12:28:49 GMT

Bill, that would limit the customer to only one.

Min Price - The lowest of the prices corresponding at Customer level for a particular combination of Catalog and NTA.

If I take the above data set as example, different cases will be as below :

a) Nothing selected in Catalog# and NTA. Click on 'Show Customer'

I want the output to be -

ABC123	x	2.4
ABC123	y	2.4

b) Nothing selected in Catalog# and NTA. Click on 'Hide Customer'

Output should be -

ABC123

2.4

c) NTA = 'A' . Click on 'Show Customer

Output should be

ABC123	x	2.4
ABC123	y	2.4

d) NTA = 'B' . Click on 'Show Customer

Output should be

ABC123	x	8.6
ABC123	y	8.6

e) NTA = 'A' . Click on 'Hide Customer'

ABC123

2.4

f) NTA = 'B' . Click on 'Hide Customer'

ABC123

8.6

Hope this helps.

Re: Conditional calculation NOT based on dimension in a table

simenkg — Wed, 16 Jul 2014 12:36:03 GMT

Hi Paramita,

We define a "Shadow table" with the minimum price per Catalog#. We do this in a variable vMinPrice defined as:
=aggr(min(Price),Catalog#)

The expression will then be:

$(vMinPrice).

I did not test it for more than one Catalog#, but I think it should work fine.

Regards
SKG

Re: Conditional calculation NOT based on dimension in a table

Wed, 16 Jul 2014 13:19:14 GMT

Thanks Alessandro