Calculated Dimension

[#1]

I have a table of Vendors (e.g., Apple, HP, Dell, Sony) and I would like to compare results from 1 selected vendor to the result of everyone else. For example, let's say that my selected Vendor is 'Dell':

Vendor Sales (ThisYr) Sales (LastYr)
Dell $100,000 $125,000
Others $500,000 $550,000

Can I use a calculated Dimension for this, where 'others' would represent an aggregated total of non-selected vendors?

[#2]

BTW...I've hardcoded a calculated dimensions as follows:

1. Vendor = 'Dell'
2. Vendor <> 'Dell'

The results are fine, but the table is displayed as follows:

1. -1
2. 0

I've tried substituting 'Dell' with vCurrentVendor, but then the results are not broken out into 2 lines as I would expect, but rather combined into one aggregated amount.

Is there a better way to do this and have my results presented as in my original post above?

[#3]

I was going to say:

=if(vendor <> GetFieldSelections(vendor),'Others',GetFieldSelections(vendor))

but it doesn't work.... 😞

however this does:

=if(vendor<>'dell','Others',vendor)

it seems that of course when you make a selection, you then get the "others" data as excluded and hence wont show. I'll have a think.

[Oleg_Troyansky]

In order to create your dimensions, you need to use "Synthetic Dimensions" - look them up in the Help Section.

The first value would be only(Vendor) and the second value would be a constant "Others".

In your expressions, you'll need to use function IF() to question the value of your synthetic dimension, in order to choose the relevant expression.

cheers,

[Anonymous]

I'm sure that you can resolve this using set analysys. There are examples in Help that you can use.

[#6]

Hi Oleg Troyansky,

Can u provide a sample application of using the synthetic dimension for the above query?????????

thanks in advance

[#7]

I will be happy to post a solution once it's done. It may take some time for me to figure it out though. QV help text on synthetic dimension functions isn't very helpful But I do believe herein lies the solution. It would allow you to select 1 or more vendors and compare them to the group that is not selected...very nice!

[Oleg_Troyansky]

Here is a simple example demonstrating the use of Synthetic Dimensions.

Enjoy!

[#9]

Hi Oleg Troyansky,

Thank you for ur reply..

its g8..............

[#10]

Hi guys,

Could you please help me with the following issue?

I want to build the following Pie Chart being able to filter by year, supplier, etc with one selected customer value.

What are the needed expression(s) in order to have this Pie Chart?

Synthetic Dimension: =ValueList('$(=only([Customer]))', 'Others')

Expression: if(ValueList('$(=only([Customer]))', 'Others') = 'Others',

                        sum({1-$} Sales),

                        sum(Sales)

                   )

Do you know how can I filter per year, supplier or product category on the first sum: sum({1-$} Sales) ?

I would like to filter by year, supplier, etc but there is the {1-$}

Selecting year, supplier, etc works only with sum(Sales).

Thank you,

- Gabriel