Dimensions with Conditions

[#1]

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

[#2]

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

[#3 · Accepted solution]

hi,

you can test

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

>= 30 days  & <60 days' )

and

sum(Stock Value)

[#4]

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

[swuehl]

Maybe you are missing a last closing bracket ) ?

[#6]

yes i think so

thanks swuenl,

[#7]

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??

[swuehl]

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?

[#9]

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

thanks all..