# 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

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

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

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

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

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

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

Honored Contributor II

## Re: If Statement Help - Qlik Sense

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

Not applicable

Thanks I did