topic Re: set analysis if statement in QlikView

set analysis if statement

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: set analysis if statement

sunny_talwar — Fri, 18 May 2018 15:06:27 GMT

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

Re: set analysis if statement

Anonymous — Fri, 18 May 2018 15:46:15 GMT

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

Re: set analysis if statement

sunny_talwar — Fri, 18 May 2018 15:57:27 GMT

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

Re: set analysis if statement

Anonymous — Fri, 18 May 2018 16:08:25 GMT

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.

Re: set analysis if statement

sunny_talwar — Fri, 18 May 2018 16:12:06 GMT

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

Re: set analysis if statement

Anonymous — Mon, 21 May 2018 10:21:36 GMT

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

Re: set analysis if statement

sunny_talwar — Mon, 21 May 2018 11:15:30 GMT

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

Re: set analysis if statement

Anonymous — Wed, 23 May 2018 09:01:32 GMT

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.

Re: set analysis if statement

Anonymous — Mon, 25 Jun 2018 15:12:43 GMT

Any luck on this? I am still stuck

Re: set analysis if statement

sunny_talwar — Mon, 25 Jun 2018 16:40:40 GMT

I am still not sure I am able to understand the required output for the data provided... can you give me the exact numerical value you would want to see in the text box object based on the data provided?

Re: set analysis if statement

Anonymous — Tue, 26 Jun 2018 08:51:58 GMT

I have attached another example which hopefully will explain it a bit more. I have split it out into 3 text boxes with a description in those.

Let me know if you need more description still

Re: set analysis if statement

sunny_talwar — Tue, 26 Jun 2018 11:44:25 GMT

All I am looking for is the numerical output that you expect to see in these text boxes... for example... tell me something like this

Sunny,

I want to see 10 in text box 1 and that is because .....

I want to see 800 in text box 2 and that is because....

I want to see 2 in text box 3 and that is because....