Solved: How to calculate rate - Qlik Community

Report Inappropriate Content · ‎2011-01-05

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 · ‎2011-01-08

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.

View solution in original post

Miguel_Angel_Baeyens · ‎2011-01-05

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

Report Inappropriate Content · ‎2011-01-05

Hi,

Thanks for your quick reply.

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

Thanks.

Report Inappropriate Content · ‎2011-01-05

Hi Issaac,

Try this one

count(Col_Name)

Hope its useful

Miguel_Angel_Baeyens · ‎2011-01-05

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.

Report Inappropriate Content · ‎2011-01-05

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

Report Inappropriate Content · ‎2011-01-05

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.

Miguel_Angel_Baeyens · ‎2011-01-05

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.

Miguel_Angel_Baeyens · ‎2011-01-05

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.

Report Inappropriate Content · ‎2011-01-06

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

Related Topics