IF and COUNT - need some help on this

[Anonymous]

Hello,

Can someone explain to me what exactly this line mean in QV?

= If((((count({<col1-={'payment'}, col2 = {'$(=max(col2))'}, _YTD = {'YTD'}>} col1)) / (count({<col1-={'payment'}, col2 = {'$(=max(col2)-1)'},_YTD = {'YTD'}>} col1)))-1)

>

0,

chr(9650),chr(9660))

I think ultimately it evaluates the expression to  if > 0, then generate 9650 else 9660.  But I need to explain in plain English

especially what that "-1" mean before the "> 0" on the 2nd line in above.

Thanks!

[Anonymous]

OK, I tried this and it worked (see below).  However I don't like this solution.  Under "Visual Cues" for this straight table chart, I used calculated text and I entered the following and it worked.  I hate to run a SUM on a resultset just to determine whether it's a positive or negative.  How do I re-write this IF() statement to evaluate the result of another

expression?  Example, in my Fig1., I already know 3.14 is positive.  How do I use this 3.14 in the below IF()?

= If(

(

(

(

sum({<_BenExpenseFlag={'1'}, fYear = {'$(=max(fYear))'},_YTD = {'YTD'}>}TransactionAmountInDollars)

)

/

(

sum({<_BenExpenseFlag={'1'}, fYear = {'$(=max(fYear)-1)'},_YTD = {'YTD'}>}TransactionAmountInDollars)

)

)

-1

)

>

0,

red(),

green()

)

[Peter_Cammaert]

IMHO aside from expression results in straight tables, you cannot refer to results of expressions in other objects.

However you can store the text of complex tedious expressions in variables and evaluate them in a thousand places by simply expanding the variable content. For example, setting a variable vFormula in your script to:

SET vFormula = ((sum({<_BenExpenseFlag={'1'}, fYear = {'$(=max(fYear))'},_YTD = {'YTD'}>}TransactionAmountInDollars) /

sum({<_BenExpenseFlag={'1'}, fYear = {'$(=max(fYear)-1)'},_YTD = {'YTD'}>}TransactionAmountInDollars))-1);

allows you to convert the expression of the IF into

= IF ($(vFormula) > 0, red(), green())

wherever you need it. And QlikView will take care of not recalculating the same expression twice (behind the scenes in the cache). This improves maintainability of complex expressions quite a lot.

Best,

Peter

[Anonymous]

Peter,

I followed exactly what you recommended.  I did the following:

1. Opened script editor.  Created a new tab at the end and place your SET vFormula code exactly how you

     had it.  I'm assuming all the QVD's need to be loaded prior to setting the vFormula because we need the data first.

2. I replaced my code with your

    = IF ($(vFormula) > 0, red(), green())

3.  Clicked OK/Apply to exit out.

The up arrow changed back to black just like in my Fig1.   My question is do I need to refresh the app after making the above changes?  I don't have all the QVD's.  I just have the QVW file.

[Peter_Cammaert]

If this is all you do, then it won't work. The vFormula variable will only contain a value if you execute the load script. And it seems you cannot run the script because you don't have any required source QVDs.

Don't worry, there is a solution for this. Open Settings->Variable Overview. Define a variable called vFormula. Select that variable name, and paste the text of the SET statement between the equal sign and the semicolon in the Definition area, then click OK.

Does your arrow change color now?

Best,

Peter

[Anonymous]

Peter,

Yes, that worked !!!.

I improved slightly.  I have now defined a couple of variables for my colors.

vColor_Red==rgb(255,0,0)

vColor_Green==rgb(34,139,34)

IF ($(vFormula) > 0, vColor_Red, vColor_Green)

This will provide me with more flexibility and greater control over the actual color.  In addition, they are re-usable.

OK, now that I got this workin.  My question is do you think I should leave it as-is or leave my original workaround code as listed below?  I will use whichever is most efficient.

This sheet actually contains a few other straight tables with the up/down arrow.  The other ones are working fine by specifying the lower and upper values and you pick the color from the GUI for the background.  I have no idea why this particular straight table is giving me this problem where I have to use calculated text.

Do you have any insight?

= If(

(

(

(

sum({<_BenExpenseFlag={'1'}, fYear = {'$(=max(fYear))'},_YTD = {'YTD'}>}TransactionAmountInDollars)

)

/

(

sum({<_BenExpenseFlag={'1'}, fYear = {'$(=max(fYear)-1)'},_YTD = {'YTD'}>}TransactionAmountInDollars)

)

)

-1

)

>

0,

red(),

green()

)

[Anonymous]

One outstanding question which I haven't seen a response yet is the question I posted on "Dec 10, 2015 5:40 PM" about the

"-1" after the division.  I am reposting the code here (see the arrows).  Is this simply to multiply the division operation by

-1 ??

= If(

(

(

(

sum({<_BenExpenseFlag={'1'}, fYear = {'$(=max(fYear))'},_YTD = {'YTD'}>}TransactionAmountInDollars)

)

/

(

sum({<_BenExpenseFlag={'1'}, fYear = {'$(=max(fYear)-1)'},_YTD = {'YTD'}>}TransactionAmountInDollars)

)

)

-1         <<<<<<<<

)

>

0,

red(),

green()

)

[datagrrl]

It is taking one away from the count of this year over the count of last year.

(Count This Year/Count Previous Year) - 1

It is just a way to find out of the count the previous year is higher/lower than the current (or max) year.

Basically if the count this year is higher than last year, the quotient will be greater than 1, so when you subtract 1, you get a number more than zero.

[Anonymous]

Can you compare the two counts without having to do a division?  Can you just simply do?

IF(ThisYearsCount > LastYearsCount, chr(9650), chr(9660))

Of course, we'll need to write the actual formula/expression for ThisYearsCount and LastYearsCount.

[datagrrl]

As Peter said above, there are lots of ways to do this. It is really up to you.

[Anonymous]

OK, sounds good.  Since I'm new to QV, I just wanted to clarify my understanding.  Personally I would never use

a division operation just to compare two numbers.   It's confusing.   Thanks for everyone's help.