Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
sskiggs1
Contributor III
Contributor III

set analysis if statement

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,

12 Replies
sunny_talwar

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

sskiggs1
Contributor III
Contributor III
Author

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

sskiggs1
Contributor III
Contributor III
Author

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?

sskiggs1
Contributor III
Contributor III
Author

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

sskiggs1
Contributor III
Contributor III
Author

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.

sskiggs1
Contributor III
Contributor III
Author

Any luck on this? I am still stuck