topic How to calculate rate in QlikView

How to calculate rate

Wed, 05 Jan 2011 09:17:02 GMT

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.

How to calculate rate

Miguel_Angel_Baeyens — Wed, 05 Jan 2011 09:24:52 GMT

Hello Isaac,

I'd create a new flag field and use it in the expression:

LOAD *, If(Len(SubOrderId) > 0, 1, 0) AS FlagHasSubOrderRESIDENT Table; DROP TABLE Table; // To avoid unwanted syn keys

Expression should look like

Count({< FlagHasSubOrder = {1} >} OrderId) / Count({1} OrderId)

Hope this helps

How to calculate rate

Wed, 05 Jan 2011 09:37:23 GMT

Hi,

Thanks for your quick reply.

But is there any other method that just uses expression directly?

Thanks.

How to calculate rate

Wed, 05 Jan 2011 09:47:21 GMT

Hi Issaac,

Try this one

count(Col_Name)

Hope its useful

How to calculate rate

Miguel_Angel_Baeyens — Wed, 05 Jan 2011 09:48:54 GMT

Since you are loading form an inline table, this expression should work

=Count({< SubOrderId = {"=IsNum(SubOrderId)"} >} SubOrderId) / Count(OrderId)

Depending on how your data source and driver deals with nulls, that set analysis may not work.

Hope this helps.

How to calculate rate

Wed, 05 Jan 2011 10:18:24 GMT

Thanks it works, but I was wondering what is the meaning of this: {"=IsNum(SubOrderId)"}, what will it return?

How to calculate rate

Wed, 05 Jan 2011 10:49:50 GMT

Hi,

I mean, if there are selection have been choosen, this rate will be changed.

I want to it keep 50% permanent. I know I need to add 1 not $ into this expression, but how to do it?

=Count({< SubOrderId = { "=IsNum(SubOrderId)" } >} SubOrderId)

Thanks.

How to calculate rate

Miguel_Angel_Baeyens — Wed, 05 Jan 2011 11:10:29 GMT

This filters so that SubOrderId has any value that can be represented as numeric. Similar to this:

Count({< CompanyName = {"=Left(CompanyName, 1) > 3"} >} OrderId)

In case you have several companies which names start in a number, this will count the number of Orders for all companies which first letter in name is greater than 3. Kind of dummy example.

How to calculate rate

Miguel_Angel_Baeyens — Wed, 05 Jan 2011 11:53:12 GMT

Hello Isaac,

I don't think it's possible because of the modifier with an explicit call to a function (IsNum()). I'd rather use a detached gauge chart (object properties, detach).

Hope this helps.

How to calculate rate

Thu, 06 Jan 2011 07:38:39 GMT

Hi,

I am confused by the set analysis, when shall I use $ or not.

For example:

If I want to get the Max month Amount based on the selection:

It works:

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

But I want to get the amount of the month before the max month, I use

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

But it does not work.

But this expression works:

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

So what's wrong with them?

How to calculate rate

Miguel_Angel_Baeyens — Thu, 06 Jan 2011 11:53:39 GMT

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.

How to calculate rate

Fri, 07 Jan 2011 02:57:04 GMT

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

How to calculate rate

Miguel_Angel_Baeyens — Fri, 07 Jan 2011 21:30:37 GMT

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.

How to calculate rate

Sat, 08 Jan 2011 10:50:34 GMT

Hi,

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

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

How to calculate rate

Miguel_Angel_Baeyens — Sun, 09 Jan 2011 02:04:34 GMT

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.

How to calculate rate

Sun, 09 Jan 2011 12:59:01 GMT

OK, thanks, now I am clear about it.

Thanks sir.