Calculated Dimension using ONLY function?

[poluvidyasagar]

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

[neelamsaroha157]

Attached it again.

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

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

[vishsaggi]

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?

[vijetas42]

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

[poluvidyasagar]

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

[vishsaggi]

Check attached?

[poluvidyasagar]

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

Thanks Vishwa

[vishsaggi]

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

[poluvidyasagar]

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

[vishsaggi]

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

[anu]

Thanks 👍