Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

If Statement Help - Qlik Sense

Good Morning,

I am currently building what I am calling an "Account Health Index" that will measure many different data points across my organization that will ultimately create a Health Score.

What I am trying to accomplish is I want to assign a specific score or grade those various different data points this score then would be used as a combined total.

Here is what I have so far...

The following formula creates a profitability value of my accounts that we deliver shipments to, what I am trying to achieve is to ad a grade based on the total of the formula below. For Example,


(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL))) > 5000 = 10 Points

(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL))) > 2000 = 5 Points

(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL))) > 0 = 0 Points

(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL))) > -2000 = -5 Points

(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL))) > 5000 = -10 Points


As always thank you all for your assistance, without the assistance of the community I would never have gotten as far as I have in build a solution for our orgnaization


Barry

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: If Statement Help - Qlik Sense

Hi Barry,

One typo error in my previous expression, try below one.  If not working share some sample file so that it would be easier to trace the issue.

=If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 5000, 10,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 2000, 5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 0 , 0,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > -2000,  -5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > -5000, -10)))))

10 Replies
MVP
MVP

Re: If Statement Help - Qlik Sense

HI,

Try like this

=If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > 5000, 10,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > 2000, 5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > 0 = 0,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > -2000,  -5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > -5000, -10)))))

Not applicable

Re: If Statement Help - Qlik Sense

Jagan,

Thank you for the reply... I did do that and then I even copied your formula and I am getting the a hyphen as the return.

Any thoughts??

Thanks

Screen Shot.PNG

neetha_p
Honored Contributor

Re: If Statement Help - Qlik Sense

Hi Barry,

Try:

=If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > 5000, 10,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > 2000, 5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > 0, 0,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > -2000,  -5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00 -(Sum(SAGMDOL)) > -5000, -10)))))

Regards

Neetha

MVP
MVP

Re: If Statement Help - Qlik Sense

Hi,

Try this in text object and let me know what value you are getting?

Try this expression

=If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 5000, 10,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 2000, 5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 0 = 0,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > -2000,  -5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > -5000, -10)))))

Also check whether FISYR is in the format of YYYY?

Regards,

Jagan.

Not applicable

Re: If Statement Help - Qlik Sense

The FISYR is in YYYY formate... the formula I was using does create a total... I seam to be having issues with getting the points (grade) applied

MVP
MVP

Re: If Statement Help - Qlik Sense

Hi Barry,

One typo error in my previous expression, try below one.  If not working share some sample file so that it would be easier to trace the issue.

=If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 5000, 10,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 2000, 5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > 0 , 0,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > -2000,  -5,

If(Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65 -(Sum(SAGMDOL)) > -5000, -10)))))

sasiparupudi1
Honored Contributor III

Re: If Statement Help - Qlik Sense

Hi

if you put the following individual statements in a text object, do you get the correct values?

Count({$<FISYR = {$(=year(Today()))}>} INVOICE)*65.00

Sum(SAGMDOL)


Sasi

Arjunarao
Honored Contributor II

Re: If Statement Help - Qlik Sense

Hi Barry, Are you able to manage to achieve? Thank you.

Not applicable

Re: If Statement Help - Qlik Sense

Thanks I did