Save Me From a Calculated Dimension

[#1]

I'm pretty sure I've encountered this type of problem before and was able to work my way around it without resorting to a calculated dimension (like I'm trying now).  

I want really want to create a box chart for this - but a pie should be easier for the exaple as everyone is familiar with it.

I have a dimension for Objects (Object_Name, Object_Size, Object_Weight).

There is a fact table that has transactions of the object, when it sold, how many, for how much  (Sale date, Object_Name, Quantity_Sold, Amount_Sold_For)

I want to have a pie chart that shows how many units were sold that are above or below a threshold that is set by an input box.

By default the variables are set like this:

vObjectSizeThreshold:  10   (cuft)

vObjectWeightThreshold: 20  (lbs)

I'm considering a calculated dimension that is like this:

=

if( Object_Weight > vObjectWeightThreshold
OR Object_Size > vObjectSizeThreshold

, 'BIG OBJECT', 'SMALL OBJECT')

And an expression that does

=sum(Quantity_Sold)

How can I do this without a calculated dimension? 

[Oleg_Troyansky · Accepted solution]

Hmmm, it's not easy to save you from a calculated dimension since the values "BIG OBJECT", "SMALL OBJECT" don't exist in your data.

I suppose you could create an Island field ObjectType with these two values and use it as a dimension. Then, in the Expression, you can test for the value of the ObjectType and formulate your Set Analysis accordingly:

IF(ObjectType = 'BIG OBJECT',

          sum({<

                    Object_Name = P({<Object_Weight={">$(vObjectWeightThreshold)"} >} Object_Name) +

                                             P({<Object_Size={">$(vObjectSizeThreshold)"} >} Object_Name)

                  >}   Quantity_Sold)

,          // Same formula, only using "-=" in Set Analysis to select all other Objects

          sum({<

                    Object_Name -= P({<Object_Weight={">$(vObjectWeightThreshold)"} >} Object_Name) +

                                             P({<Object_Size={">$(vObjectSizeThreshold)"} >} Object_Name)

                  >}   Quantity_Sold)

)

There is a lot to absorb and to explain here... Please don't hesitate to ask if any components of the formula are not clear to you.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

[#3]

I absolutely love this solution.   It made me stop, think and look things up.    First couple attempts I couldn't get it working but was able to figure my way through how it works.

So now I have a good number showing on my chart.... but when I click it does nothing.

Is there a way to filter to those "Objects" that are either BIG or SMALL when it is clicked?

[Oleg_Troyansky]

Well, now you are taking it back to the Calculated Dimension... The only way (that I can think of) to tie the "object type" back to the Objects dynamically is through a Calculated Dimension that implements an AGGR() function with Object_Name as a dimension:

AGGR(

     if( Object_Weight > $(vObjectWeightThreshold)
     OR Object_Size > $(vObjectSizeThreshold)

     , 'BIG OBJECT', 'SMALL OBJECT')

, Object_Name)

and then a simple expression:

=sum(Quantity_Sold)

This time, selecting one of the types should also select the associated Objects.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

[#5]

The AGGR really made the difference.   Otherwise I don't know what it was trying to filter to - butit it wasn't working how I expected prior to adding AGGR.

Thanks - this really helped me understand what is going on with this.