If Condition is not working as expected in script or expression

[krishna20]

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

[Anil_Babu_Samineni]

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

[krishna20]

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]))))

[forte · Accepted solution]

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

[krishna20]

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

[Anil_Babu_Samineni]

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')

[krishna20]

Hi Anil,

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

[Anil_Babu_Samineni]

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.

[krishna20]

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 😞

[chrismarlow]

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.