topic Re: How to Ignore field selection which is in dimension using set analysis in QlikView

How to Ignore field selection which is in dimension using set analysis

Fri, 26 Jul 2013 21:44:44 GMT

Hi All,

I am facing problem in the below mention calculation:

Product	Market	units
A1	A	207454
A2	A	5457
A3	A	457
A4	A	356
A5	A	5724
B1	B	2457
B2	B	4577
B3	B	23475
C1	C	756
C2	C	754275
C3	C	324
C4	C	57425
C5	C	4257
C6	C	547547

Now I have a straight table in QlikView wherein my dimension is: If(Product='A1' or Product='B1' or Product='C1', Product) so that it shows only three products

and my expression for market share is sum({<xyz>} units / Aggr(sum({<xyz>}units),Market) and it works absolutely fine, but the moment I select any particular brand(Say A1) in the list box then the denominator of the expression returns value only for the selected product and for other two markets/products it becomes ZERO.

It will be of great help if anyone can help me out.

Regards

Sagar Gupta

Re: How to Ignore field selection which is in dimension using set analysis

israrkhan — Fri, 26 Jul 2013 22:52:30 GMT

Hi,

if you want to discard the selections than Use. 1

sum(1{<xyz>} units / Aggr(sum(1{<xyz>}units),Market),

if want to discard the Dimension then use total keyword like below

sum({<xyz>} total units / Aggr(sum({<xyz>} total units),Market),

Hope it help...

Re: How to Ignore field selection which is in dimension using set analysis

Sat, 27 Jul 2013 00:30:32 GMT

Hello Sagar

Use Dimension plus equal like "sum({<Dimension=>} expression)".

It will sum the expression ignoring the defined dimension.

Hope it helps!

Re: How to Ignore field selection which is in dimension using set analysis

jagan — Sat, 27 Jul 2013 05:28:12 GMT

Hi,

If you DimensionName= in an expression, then the set analysis expression doesn't consider the selections of that particular Dimension.

Sum({<DimensionName=>} Sales)

Regards,

Jagan.

Re: How to Ignore field selection which is in dimension using set analysis

Sat, 27 Jul 2013 10:38:54 GMT

Hi Usman,

I tried what you suggested but its not working in this case. 😞

Regards

Sagar

Re: How to Ignore field selection which is in dimension using set analysis

Sat, 27 Jul 2013 10:42:00 GMT

Hi Leo,

I have already done this and it is not working. I am not getting the values for excluded products the moment I select Product.

Note: I am getting correct value for the selected Product in the table.

Regards

Sagar Gupta

Re: How to Ignore field selection which is in dimension using set analysis

Sat, 27 Jul 2013 10:43:05 GMT

No dude Its not working. I tried all the above mentioned syntax. 😞

Regards

Sagar

Re: How to Ignore field selection which is in dimension using set analysis

kaushiknsolanki — Sat, 27 Jul 2013 10:44:38 GMT

Dear Sagar,

If you want to see only the 3 Products which are A1,B1 and C1 then the better idea is to create a field in script instead of using the if statment in the dimension.

Try this, this might solve your problem.

Regards,

Kaushik Solanki

Re: How to Ignore field selection which is in dimension using set analysis

Sat, 27 Jul 2013 12:53:30 GMT

Hi Kaushik,

Thanks for the reply. I don't think doing this will solve the problem. Yes I agree that we shouldn't use calculated dimension as it decreases the tool's performance.

Regards

Sagar Gupta

Re: How to Ignore field selection which is in dimension using set analysis

kaushiknsolanki — Sun, 28 Jul 2013 06:49:03 GMT

Dear Sagar,

Give it a try and see whether it works or not.

Regards,

Kaushik Solanki