topic Re: If Condition is not working as expected in script or expression in QlikView

If Condition is not working as expected in script or expression

krishna20 — Fri, 15 Nov 2024 23:40:24 GMT

Hi Folks,

Very strange, simple If condition is not working as expected. Whenever I used in the text box, it shows null and in the script, It's throwing the error as invalid expression. Can anyone suggest me why it behaves like this?

If condition I am loading at load statement in the script by taking resident.

IF([Type]='Champion',0,IF(Sum([Sales])<=(Sum([LP])*Sum([Quantity])),(Sum([ Net Price])*Sum([Discount])))) as Test

The expected output is, Type=Champion is not there in my current data, It should goes to else part to Second If condition.

Sum(Sales) =1158 and Sum(LP)* Sum(Quantity) =1158 so, I need Sum(Net Price)* Sum(Discount) amount.

Thanks

Krishna

Re: If Condition is not working as expected in script or expression

Anil_Babu_Samineni — Fri, 06 Nov 2020 10:16:41 GMT

Hi, You have include Else part for both fail cases like

IF([Type]='Champion',0,IF(Sum([Sales])<=(Sum([LP])*Sum([Quantity])),(Sum([ Net Price])*Sum([Discount])), (Sum([ Net Price])*Sum([Discount])))) as Test

Re: If Condition is not working as expected in script or expression

krishna20 — Fri, 06 Nov 2020 10:30:17 GMT

Hi Anil,

Thanks for your reply.

I cannot do that because, I am having another condition for else. The if condition is failing at stage 2 itself.

My Actual expression is

If([ Type]='Champion',0,
if(Sum([Sales])<=(Sum([LP])*Sum([Quantity])),(Sum([ Net Price])*Sum([Discount]))
, (Sum([Sales])-(Sum([Promo LP])*Sum([Quantity])))/(Sum([Sales])*Sum([Net Price]))))

Re: If Condition is not working as expected in script or expression

forte — Fri, 06 Nov 2020 11:10:22 GMT

Hi @krishna20

If you need aggregation functions ( as sum, count etc) loading from a table (resident via in your case), you need to use group by clause to tell Qlik for wich dimension you want calculate.

That's why you are receiving such "invalid expression" script error.

Hope it helps

Regards

Re: If Condition is not working as expected in script or expression

krishna20 — Fri, 06 Nov 2020 12:16:12 GMT

Yes, I notified that. Thanks for reminding me again. But , I am mainly focusing on writing the expression on front end in the chart.

Re: If Condition is not working as expected in script or expression

Anil_Babu_Samineni — Fri, 06 Nov 2020 13:13:31 GMT

If that is the case, How come it is multiplying with LP?

(Sum([LP])*Sum([Quantity]))

I believe, There is no script error during reload. If so, try this?

IF(Sum([Sales])<=Sum([Quantity]),(Sum([ Net Price])*Sum([Discount]), 'Else Condition If needed')

Re: If Condition is not working as expected in script or expression

krishna20 — Fri, 06 Nov 2020 16:08:53 GMT

Hi Anil,

It is my requirement and condition should satisfies with Sum([Sales])<=(Sum([LP])*Sum([Quantity]). We cannot ignore Sum(LP)*Sum(Quantity).

Re: If Condition is not working as expected in script or expression

Anil_Babu_Samineni — Fri, 06 Nov 2020 18:25:57 GMT

The reason I asked because of your explanation (In that there is nothing LP)

Anyway, What are you getting using this

IF(Sum([Sales])<=(Sum(LP)*Sum([Quantity])),(Sum([ Net Price])*Sum([Discount]), 'Else Condition If needed')

Can you please demonstrate the similar issue in sample applicaiton which may help quicker.

Re: If Condition is not working as expected in script or expression

krishna20 — Sun, 08 Nov 2020 10:43:13 GMT

Hi Anil,

Unable to share sample app due to data privacy, I am attaching snippet from my actual data.

IF([Type]='Champion',0,IF(Sum([Sales])<=(Sum([LP])*Sum([Quantity])),(Sum([ Net Price])*Sum([Discount]))))

Just elaborating my expression, I have Type = 'Volume' not Champion. So, first condition fails and goes to else condition. Sum(Sales)=135,205 and LP* Quantity= 135,205. Second condition satisfies and should go to Netprice * Discount=14,499. But, It's displaying '-'.

I am not getting, how it is working in this situation 😞

Re: If Condition is not working as expected in script or expression

chrismarlow — Sun, 08 Nov 2020 14:11:12 GMT

Hi,

This could be a total red herring, but noting you have a space at the start of [ Net Price], is that actually in your fields or an issue when copying over? Do you get an error in the UI when you see '-' in text box?

I think in the original post you mentioned using in the load script - if you did not have appropriate grouping you would get Invalid Expression error, that might be worth investigating.

From experience I think you may also need to look at the order you sum in, depending how you aggregate, so if Discount were a percentage say I think I would expect to see Sum([Net Price]*[Discount]) rather than Sum([Net Price])*Sum([Discount]) ... but there may be some quirks in your data model that are not obvious.

Cheers,

Chris.

Re: If Condition is not working as expected in script or expression

krishna20 — Mon, 09 Nov 2020 04:35:05 GMT

Hi Chris,

Thanks for yours input. As I already mentioned above in the comments, my first priority is to write the expression at front end only.

I am using the fields in the expression are correctly and it is not highlighting any error in the expression window.

This is very strange behavior I am facing with Qlik, no clue how it is working.

Re: If Condition is not working as expected in script or expression

Anil_Babu_Samineni — Tue, 10 Nov 2020 16:59:51 GMT

Hey, I don't see any issue from your screenshot? Do you think this is not working as expected (I can assume only based on Measure titles)?

IF([Type]='Champion',0,IF(Sum([Sales])<=(Sum([LP])*Sum([Quantity])),(Sum([ Net Price])*Sum([Discount]))))

From above, Red part will fail, So condition will fulfill from blue part and get the result of Green part? Is that not what you are expecting?

Re: If Condition is not working as expected in script or expression

krishna20 — Mon, 30 Nov 2020 05:50:03 GMT

Hi Anil,

Yes, I need the green part to be calculated. I fixed this issue by taking all above expression to backend and group by with all the dimensions. Don't know why it was not calculating at front end even though the second condition satisfies. Thanks for your inputs and @forte .

Thanks

Krishna