set analysis if statement

[Anonymous]

I have a statement which I am finding it hard to get my set analysis correct in (i think this would be the best approach)

My statement needs output this:

If the sum of onhand* cost is less or equal to the sum of the use type 'Gross Requirements' in period value but only in bucket 1 then output 1,

If not,

the sum of onhand* cost is less or equal to the sum of the use type 'Gross Requirements' in period value but in bucket 1 and 2 then output 2,

If not ,

the sum of onhand* cost is less or equal to the sum of the use type 'Gross Requirements' in period value but in bucket 1 and 2 and 3 then output 3,

I need this to be repeated up to 36 buckets

At the moment i have this.......

=if(sum(ONHAND_QTY*COST) <= (sum({<USE_DESC_TYPE={"Gross Requirements Amt"}>}PERIOD_VAL and BUCKET_NUMBER = 1),1,

My problem with this is that Bucket number is just a field that is silled with 1,2,3,4,5,6,- 36 so cannot be summed.

If anyone could help that would be greatly appreciated,

[sunny_talwar]

Would you be able to share some raw data with expected output from the data you will provide?

[Anonymous]

Please see attached. I know the expression is very wrong but I am hoping it gives an idea of what I need?

[sunny_talwar]

Would you be able to paint a picture of what you intend to see? A chart? a text box object? what exactly are you hoping to see as an output... it will help if you can give the exact numbers

[Anonymous]

Sorry I forgot to add in Part Number.

I want it so that it will only work one part number at a time. When the part number is selected then the relevant if statement will be displayed in a text box.

[sunny_talwar]

But what exactly does it need to display in the text box?

[Anonymous]

eg,

=if(sum(ONHAND_QTY*COST) <= (sum({<USE_DESC_TYPE={"Gross Requirements Amt"}>}PERIOD_VAL and BUCKET_NUMBER = 1),1

the sum of onhand* cost is less or equal to the sum of the use type 'Gross Requirements' in period value but in bucket 1 then 1 would need to be shown in the text box, If the statement does not fit the if statement then it will do onto the next if statement, if that is correct output if not go onto the next statement and so on

[sunny_talwar]

But there are so many rows in table... you can to do this for each Part_Number based on selection of a single Part_Number? aggregated at all levels? You will have to give better direction on what you expected output needs to be in order for us to help you better here

[Anonymous]

Hi Yes, by a single part number, this can either be in a text box or can be added as an expression in a table. I understand it will not work at all levels, all I need is by part number when selected.

[Anonymous]

Any luck on this? I am still stuck