9 Replies Latest reply: May 17, 2012 1:23 PM by groizner RSS

Calculated Dimension

Julie Stephens

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
    Julie Stephens

    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
    adamvaughan

    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

      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
        Ashok Kumar Jayabal

        Hi Oleg Troyansky,

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

        thanks in advance

         

         

        • Calculated Dimension
          Julie Stephens

          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!

           

        • Calculated Dimension
          Oleg Troyansky

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

          Enjoy!

          • Calculated Dimension
            Ashok Kumar Jayabal

            Hi Oleg Troyansky,

            Thank you for ur reply..

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

            • Calculated Dimension
              groizner

              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

  • Calculated Dimension
    Michael Solomovich

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