Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

Anonymous
Not applicable
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

Anonymous
Not applicable
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?

Anonymous
Not applicable
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

Anonymous
Not applicable
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.

Anonymous
Not applicable
Author

Any luck on this? I am still stuck