topic Re: Calculated Dimension using ONLY function? in QlikView

Calculated Dimension using ONLY function?

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

Hi,

I am trying to achieve the following:

Sample Data:

Location	Sublocation	Part
A	A1	12345
B	B1	23456
B	B2	12456
C	C1	14678
C	C2	18992
D	D1	18320
D	D2	10302
AB	AB1	10320
AC	AB2	13920
AD	AB3	10401

My Dimensions are:

1. Location

2. Sublocation

However, I do not want to show all Locations as my dimension. I would like to use ONLY function with set analysis ?

I have tried the following but it does not seem to work:

Only ({<{Location-={'AB','AC','AD'}>}Location}

Basically i do not want to show locations with AB, AC, AD. Since this is a sample data set, i am only showing few location. However, i have quite a few locations that i want to show and exclude some. That is why i am going with not contain in set analysis.

Can someone help me on this?

I have attached the sample file.

Thanks,
Vidya

Re: Calculated Dimension using ONLY function?

PrashantSangle — Tue, 19 Jun 2018 12:50:42 GMT

Try this

if(wildmatch(Location,'AB','AC','AD'),Location)

Regards,

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 12:55:12 GMT

It does not work

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 13:04:17 GMT

stalwar1‌

Hi Sunny,

Can you help me on this one?

Re: Calculated Dimension using ONLY function?

jonathandienst — Tue, 19 Jun 2018 13:07:04 GMT

Try this:

Aggr(Only ({<{Location-={'AB','AC','AD'}>} Location), Location)

Re: Calculated Dimension using ONLY function?

neelamsaroha157 — Tue, 19 Jun 2018 13:11:10 GMT

Check this out...added expression with if condition as well as set analysis.

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 13:20:48 GMT

It does not work

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 13:21:11 GMT

I can't see anything attached

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 13:26:57 GMT

This worked for me:

=if(Not Match(Location, 'AB','AC','AD'),Location)

Check suppress when value is null

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 13:28:29 GMT

I would still like to see if this can be achieved using Only function with set analysis

swuehl‌

Re: Calculated Dimension using ONLY function?

neelamsaroha157 — Tue, 19 Jun 2018 13:28:58 GMT

Attached it again.

=Aggr(only({<Location -={$(vLocationExclude)}>}Location), Location)

where I have declared a variable as vLocationExclude = 'AB','AC','AD'.

Re: Calculated Dimension using ONLY function?

vishsaggi — Tue, 19 Jun 2018 13:44:59 GMT

Downloaded your sample file and used Jonathan expr and it worked. Can you show us what was the formula you are using. Are you using atleast one expr?

Re: Calculated Dimension using ONLY function?

vijetas42 — Tue, 19 Jun 2018 13:47:41 GMT

first of all take a straight table and in dimension use expression

=aggr(only({<Location-={'AB','AC','AD'}>}Location),Location)

and mark suppress when values are null and in expression add 1 and from presentation tab you can hide column 1(expression)

so it would show you desired result

Regards,

Vijeta

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 19:03:31 GMT

Here is the attchement... It does not seem to work

Re: Calculated Dimension using ONLY function?

vishsaggi — Tue, 19 Jun 2018 19:12:21 GMT

Check attached?

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 19:18:53 GMT

I think I had a syntax issue ... I have corrected it now.

Thanks Vishwa

Re: Calculated Dimension using ONLY function?

vishsaggi — Tue, 19 Jun 2018 19:20:41 GMT

Can you close the thread accordingly. I think there is an extra brace '{' in your set syntax.

Re: Calculated Dimension using ONLY function?

poluvidyasagar — Tue, 19 Jun 2018 19:31:11 GMT

How do i close the thread? I can't find an option to mark it as correct.

Re: Calculated Dimension using ONLY function?

vishsaggi — Tue, 19 Jun 2018 19:43:20 GMT

Logout and login to your account and go to this thread again you should see something like Correct Answer.

Re: Calculated Dimension using ONLY function?

anu — Wed, 15 Jul 2020 14:14:59 GMT

Thanks 👍