10 Replies Latest reply: Sep 4, 2015 7:48 AM by Barry Taylor

# 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

• ###### 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)))))

• ###### 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

• ###### 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.

• ###### 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

• ###### 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)))))

• ###### 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

• ###### 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

• ###### Re: If Statement Help - Qlik Sense

When I tried that it did, but got it resolved.