If Statement Help - Qlik Sense

[#1]

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

[jagan]

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

[#3]

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

[Anonymous]

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

[jagan]

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.

[#6]

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

[jagan · Accepted solution]

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]

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

[qlikviewwizard]

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

[#10]

Thanks I did