topic Dimensions with Conditions in QlikView

Dimensions with Conditions

Wed, 04 Jan 2012 16:18:59 GMT

Hi All,

I’ve got a data for 60 thousand articles. Here is the data structure;

Column A = Article Number

Column B = Lead Time

Column C = Stock Value

I want to create a table that gives me the total stock value of lead time brackets. Basically I need a very simple table that should look like this;

Lead Time Stock Value

< 30days £ 1,320,000

>= 30 days & <60 days £ 2,300,000

>= 60 days £ 4,200,000

So what's my stock value of the items with less than 30 days lead time, what's my stock value of the items 30 to 60 days lead time and finally stock value of items with a lead time of more than or equal to 60 days.

Any idea?

cheers,

mem

Dimensions with Conditions

Thu, 05 Jan 2012 09:35:52 GMT

Desperately looking for an answer to this, could someone at least tell me if it's possible or not??

Dimensions with Conditions

Thu, 05 Jan 2012 10:38:08 GMT

hi,

you can test

if(Lead Time<30, '< 30 day', if( Lead Time>=60, '>= 60 days', '

>= 30 days & <60 days' )

and

sum(Stock Value)

Dimensions with Conditions

Thu, 05 Jan 2012 10:58:12 GMT

Hi syukyoZHU,

I've created a Calculated Dimensin with;

if(Lead Time<30, '< 30 day', if( Lead Time>=60, '>= 60 days', '>= 30 days & <60 days' )

but I'm getting an "Error in Expression". Where am I going wrong?

Cheers,

mem

Dimensions with Conditions

swuehl — Thu, 05 Jan 2012 12:02:33 GMT

Maybe you are missing a last closing bracket ) ?

Dimensions with Conditions

Thu, 05 Jan 2012 12:09:31 GMT

yes i think so

thanks swuenl,

Dimensions with Conditions

Thu, 05 Jan 2012 14:26:10 GMT

Error has disappeared but unfortunately outcome is not what we need.

This is the final version of Dimension

=if([Lead Time]<30, '< 30 days', if( [Lead Time]>=60, '>= 60 days', '>= 30 days & <60 days' ))

and the Expression is

=Sum(Stock Value)

What I'm getting is a table with single row, and first column says "< 30 day" and in second column total stock value of everything (not just less than 30 days lead time items). Any ideas??

Dimensions with Conditions

swuehl — Thu, 05 Jan 2012 14:37:10 GMT

Your result for < 30 day lead time is the sum of all stock values: 7,620,000 Pounds?

Your data model with one table and three fields doesn't look too complicated, so let's look at the field format:

What is the format of your lead time field? Is it numerical integer?

And your stock value is also numerical, just formatted as Currency?

Dimensions with Conditions

Thu, 05 Jan 2012 14:50:53 GMT

It's working now.. I don't understand what went wrong a few minutes back..

thanks all..