Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
Hi,
Thanks for your quick reply.
But is there any other method that just uses expression directly?
Thanks.
Hi Issaac,
Try this one
count(Col_Name)
Hope its useful
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.
Thanks it works, but I was wondering what is the meaning of this: {"=IsNum(SubOrderId)"}, what will it return?
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.
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.
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.
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?