topic Re: Garbage after expression in App Development

Garbage after expression

Anonymous — Thu, 04 Jan 2018 10:01:20 GMT

Dear community,

I am developing a Qlik Sense App and struggle with the following KPI formula:

=IF(-(SUM(Target_rev * year_flag)-SUM(Erloese_Netto*year_flag))/Sum(Target_rev) = 0, '' , -(SUM(Target_rev * year_flag)-SUM(Erloese_Netto*year_flag))/Sum(Target_rev) )

The error message is:

Garbage after expression '=IF(-(SUM(Target_rev * year_flag)-SUM(Erloese_Netto*year_flag))/Sum(Target_rev) = 0, '' , -(SUM(Target_rev * year_flag)-SUM(Erloese_Netto*year_flag))/Sum(Target_rev) )'

Can you help me finding the error?

Thanks a lot.

Gregor

Re: Garbage after expression

sunny_talwar — Thu, 04 Jan 2018 12:23:46 GMT

Would you be able to share a screenshot of the error message and the expression

Re: Garbage after expression

luismadriz — Thu, 04 Jan 2018 12:36:46 GMT

Hi, I replicated those fields and created a KPI object with that formula and worked perfectly. Can it be field types? Are all of those fields or are you bringing variables?

I just saw Sunny's replies, so you're in good hands,

Cheers,

Luis

Re: Garbage after expression

Anonymous — Thu, 04 Jan 2018 12:48:48 GMT

Hey Sunny and Luis,

first of all thanks for your requests.

For some reason, without having changed anything, the garbage error disappeard. Nevertheless, the KPI shows no result at all.

Attached you will find a screenshot for the KPI formular and a screenshot for the manually calculated variable year_flag. All other variables in the formular are just input data that is without an error because e.g. another KPI using the same input data is working properly:

=-(SUM({1}Target_rev)-SUM({1}Erloese_Netto))/Sum({1}Target_rev)

Can you solve this issue?

Thanks a lot.

Gregor

Re: Garbage after expression

sunny_talwar — Thu, 04 Jan 2018 12:54:20 GMT

Would you be able to check if this is working or not

-(Sum(Target_rev * year_flag)-Sum(Erloese_Netto*year_flag))/Sum(Target_rev)

If this is working, then we know that the issue is somewhere in the if statement... if not, then we can focus on fixing the above expression

Re: Garbage after expression

Anonymous — Thu, 04 Jan 2018 13:43:32 GMT

Your provided formula doesn't work. It simply displays 0,00%.

The goal should actually be that if I filter for a month, the KPI should show me the percentage deviation.

Re: Garbage after expression

sunny_talwar — Thu, 04 Jan 2018 13:48:04 GMT

Your provided formula doesn't work.

My provided formula? Hahahaha, I just asked you to test this expression for me... and this is just the false part of the if condition.... so, I guess we know that the problem is in the above expression

-(Sum(Target_rev * year_flag)-Sum(Erloese_Netto*year_flag))/Sum(Target_rev)

Can you further break it down into three expressions

1) Sum(Target_rev * year_flag)

2) Sum(Erloese_Netto*year_flag)

3) Sum(Target_rev)

I am guessing that the third expression will not have any issues... It appears to be an issue with multiplication with year_flag variable....

Out of curiosity, what happens if you remove the = sign from your variable definition and then try it these two ways

Sum(Target_rev * year_flag)

and

Sum(Target_rev * $(year_flag))

Does one of these two work for you?

Re: Garbage after expression

Anonymous — Thu, 04 Jan 2018 14:18:26 GMT

Based on your support I could find the error, thank you very much.

I figured out that the year_flag is the issue. As I am still working with data from 2017 and we have already arrived in 2018, the year flag for my data set was "0" in all cases. As a consequence, my KPI could just display "0".

Removing the year_flag solves the issue and provides me with the right results.

I'm sorry for having bothered you with such a simplistic error

Gregor

Re: Garbage after expression

sunny_talwar — Thu, 04 Jan 2018 14:23:51 GMT

Glad that it finally worked for you. Please close this thread by marking correct and helpful responses

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Garbage after expression

sunny_talwar — Thu, 04 Jan 2018 14:27:28 GMT

Please do not randomly mark any response as correct.... only mark those responses which are truly correct and helpful which will help future visitors to this post.

Thank you,

Sunny

Re: Garbage after expression

sunny_talwar — Thu, 04 Jan 2018 14:29:49 GMT

Thank you sir!!