How to calculate rate

[#1]

Hi All,

I have one table:

load * Inline
[OrderId,SubOrderId
1,1
2,2
3
4
]

I want to calculate how many records have sub order, in this case, it is 2/4 .

Could you help me out?

Thanks.

[Miguel_Angel_Baeyens]

Hello Isaac,

There are two things here:

In Set Analysis, "$" as a part of the set or preceding the set modifier means "the current state of selections", that is the filters specified in Set Analysis will be done on the basis of the already done. You don't need to set this.

Sum({$< Country = {'ES'} >} Amount) or Sum({< Country = {'ES'} >} Amount)

Both will return the same results.

Don't mix that up with the dollar expansion for functions or variables that may or may not be used in Set Analysis. As a general rule, every time you want to evaluate a variable (not only show its content, but evaluate the content and return it result) you will need to use the $(). Example:

SET vSum = 1 + 1;// Now vSum will return the string "1 + 1"// But $(vSum) will return "2"

In your case, $(=Max()) is evaluating the function and returning its result, and that's why it's working.

Hope this helps.

[#3]

Thanks,

But what's the meaning of the bold expression: =sum({$<MonthOfYear={"=Max(MonthOfYear)"}>}Amount)?

Is it a set or function? If it is a function, why does not need to use $ and just use double quote?

I know the above set works, but if I want to use this form that does not use $ to get the amount that is before the max month:

=sum({$<MonthOfYear={ "=Max(MonthOfYear)-1"}>}Amount)

It does not work, it the responses the same result with =sum({$<MonthOfYear={"=Max(MonthOfYear)"}>}Amount).

I am sunk!

Thanks sir.

Isaac Li

[Miguel_Angel_Baeyens]

Hello Isaac,

The bold part is a function within a set analysis modifier. It's used for example when you need to call the same field you are using in the field.

Sum({< CompanyID = {"=Left(CompanyID, 2) = 'ES'"} >} Amount)

That will sum all amounts where company starts in "ES".

Sum({< CompanyID = {"=Sum(Expenses) > 1000"} >} Amount)

That will sum all amounts for companies that have expenses greater than 1000.

But if you want to get the value itself (in your case, the month previous to the last month loaded) that is a result of an evaluation to be passed on to the filter to select those values, then the proper syntax to use a function within set analysis is using dollar expansion:

Sum({< Date = {'>=$(=YearStart(Today()))'} >} Amount)

Hope this helps.

[#5]

Hi,

I get it, but why I can not use "-1" in the set analysis?

sum({$<MonthOfYear={ "=Max(MonthOfYear)-1"}>}Amount)

[Miguel_Angel_Baeyens · Accepted solution]

As I told above, that needs to be evaluated (it's not a result by itself), so that's why you need to do

sum({$<MonthOfYear={ '$(=Max(MonthOfYear)-1)'}>}Amount)

"1" is a numeric value, it doesn't need to be evaluated, and as such, it may be used as a field filter selection in set analysis or in the script.

"1 + 1" its not a numeric value, unless it's evaluated. It may be a string that for any reason you want to keep that way. Note that there are not data types as such in QlikView, rather than numeric and literal representations of any value, and that's why you need functions.

It's a question of syntax, as it happens in any other programming languange. Functions usually need to be evaluated.

Hope this helps.

[#7]

OK, thanks, now I am clear about it.

Thanks sir.