Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated Dimension

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?

9 Replies
Not applicable
Author

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?

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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

Not applicable
Author

Hi Oleg Troyansky,

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

thanks in advance

Not applicable
Author

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 Zip it! 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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Enjoy!

Not applicable
Author

Hi Oleg Troyansky,

Thank you for ur reply..

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

Not applicable
Author

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.

part-to-whole.JPG

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