Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Would you be able to share a screenshot of the error message and the expression
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
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
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
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.
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?
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
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
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