topic Re: Custom Range Functions for e.g. Above with count - in expression (no script) in QlikView

Custom Range Functions for e.g. Above with count - in expression (no script)

Anonymous — Wed, 23 Aug 2017 08:49:15 GMT

Hi,

having an expression like Above(Sales,1,3) I would like to be able to continue to work with the resulting "Array" or "Range" of the result.

For example I want to Aggregate that range in a way, the Range functions do not offer:

Concat( Above(Sales,1,3) , ';' )

or do calculations on them and then aggregate:

RangeSum( Above(Sales,1,3) > 500 )

How would that be possible?

And if not, would it be possible with Macros+VBScript? If yes, how?

PS: Working with QlikView

Re: Custom Range Functions for e.g. Above with count - in expression (no script)

marcus_sommer — Wed, 23 Aug 2017 11:48:51 GMT

I'm not quite sure what do you want to do but I think most of your calculations will be possible with a combination of a range- and interrecord-function like rangesum() and above(). Maybe you are missing an inner aggregation and/or a TOTAL within the above - I mean something like this:

RangeSum(Above(TOTAL sum(Sales),1,3)) > 500

which should be return a true or false for this comparing and by including it within an if-loop you could decide what should be done respectively returned.

- Marcus

Re: Custom Range Functions for e.g. Above with count - in expression (no script)

sunny_talwar — Wed, 23 Aug 2017 12:05:47 GMT

What do you be able to share few rows of data with the expected output from it?

Re: Custom Range Functions for e.g. Above with count - in expression (no script)

Anonymous — Wed, 23 Aug 2017 12:09:11 GMT

Hi Marcus,

It might have been misleading to use "Sales" in my example, because in most examples a table shows the aggregation "Sum(Sales)".

However, it doesn't make a difference - Let's assume, I just have a single Sales value per row:

Name	Sales
Alf	80
Ben	10
Charlotte	50
Dora	30
Elvis	20

I want to do for every row the following:

Take the X (lets say 3) rows above me (Above(Sales, 1, 3)) and compare each with a value Y (lets say 25).

I would have for Elvis (using >25) an Array: [True, True, False]; for Charlotte: [False, True].

With that Array I want to continue working.

Just an example: Count, how often it is True

PS: Is there a way to edit my initial question to clarify it?

Re: Custom Range Functions for e.g. Above with count - in expression (no script)

marcus_sommer — Wed, 23 Aug 2017 12:30:06 GMT

Maybe this one:

- Marcus

Re: Custom Range Functions for e.g. Above with count - in expression (no script)

Anonymous — Wed, 23 Aug 2017 13:37:18 GMT

Brilliant application of the associative and commutative property.

I will see, how far I can get with that.

Thank you.

Re: Custom Range Functions for e.g. Above with count - in expression (no script)

Anonymous — Wed, 23 Aug 2017 14:22:36 GMT

I just realized, that this only solves one of the Questions,

but how would you do the second, with the concatenate?

So we can have for elvis the string "30-50-10", for Dora: "50-10-80".

Re: Custom Range Functions for e.g. Above with count - in expression (no script)

sunny_talwar — Wed, 23 Aug 2017 14:36:36 GMT

Something like this

Re: Custom Range Functions for e.g. Above with count - in expression (no script)

sunny_talwar — Wed, 23 Aug 2017 15:14:11 GMT

Or this