topic Calculated Dimension in QlikView

Calculated Dimension

Tue, 03 Nov 2009 02:07:04 GMT

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?

Calculated Dimension

Tue, 03 Nov 2009 02:23:05 GMT

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?

Calculated Dimension

Tue, 03 Nov 2009 02:24:40 GMT

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.

Calculated Dimension

Oleg_Troyansky — Tue, 03 Nov 2009 02:49:04 GMT

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,

Calculated Dimension

Anonymous — Tue, 03 Nov 2009 07:44:35 GMT

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

Calculated Dimension

Tue, 03 Nov 2009 18:59:29 GMT

Hi Oleg Troyansky,

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

thanks in advance

Calculated Dimension

Tue, 03 Nov 2009 22:04:15 GMT

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!

Calculated Dimension

Oleg_Troyansky — Wed, 04 Nov 2009 10:07:06 GMT

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

Enjoy!

Calculated Dimension

Wed, 04 Nov 2009 12:35:39 GMT

Hi Oleg Troyansky,

Thank you for ur reply..

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

Calculated Dimension

Thu, 17 May 2012 17:23:22 GMT

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