Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate rate

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.

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

15 Replies
Miguel_Angel_Baeyens

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

Not applicable
Author

Hi,

Thanks for your quick reply.

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

Thanks.

Not applicable
Author

Hi Issaac,

Try this one

count(Col_Name)

Hope its useful

Miguel_Angel_Baeyens

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.

Not applicable
Author

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

Not applicable
Author

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

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

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.

Not applicable
Author

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?