topic Re: Count-if with max function in QlikView

Count-if with max function

Thu, 14 Nov 2013 16:54:14 GMT

Hi all,

I have an issue that I hope that one of you already handle it already.

In my model I manage information on huge amount of parameters. each parameter has an ID called System_Key.

For each system key there are a lot of samples. each sample has a unique number per System_Key.

The numbers for the samples are being generated according the chronological order.

In one of my measurements I'd like to calculate for each System_Key only its last 30 samples.

After I didn't find any set analysis expression that might help, I decided to go to count-if...

Still, it didn't work out for so I started to try to isolate the problem, till I got to this:

supposed I'd like to count the number of samples which are bigger than the maximum sample number -30.

the expectation is that for each System_Key we'll get either 30 or less. The expression I had thought of is as the following:

=count(if(SampleNumber>=RangeMax(max(SampleNumber)-30,0),SampleNumber)).

But it doesn't work. as I went deeper with it, I understood that the QV doesn't allow to have count(max()) function.

So after this very long story, does anyone have any work around to it?

Thanks a lot in advance for those of you that made till the end of the message

Hopefully one of you will be my rescuer!!

Re: Count-if with max function

jerem1234 — Thu, 14 Nov 2013 18:27:13 GMT

Have you tried set analysis with your if statement? Maybe try like this:

=count({<SampleNumber = {'>$(=RangeMax(max(SampleNumber)-30,0))'}>}SampleNumber)

or maybe a variable:

=count({<SampleNumber = {'>$(vMax)'}>}SampleNumber)

with value of =RangeMax(max(SampleNumber)-30,0) for vMax

Hope this helps!

Re: Count-if with max function

Thu, 14 Nov 2013 18:37:14 GMT

Hi,

Thank you for your replay!!

What you have suggested I've already tried.

The problem with it, is that I get the Max(SampleNumber) for all System_Key, and not for specific one.

For example, if I have System_Key with 100 samples , one with 80 and one with 50, for the 1st I'll get 30 for the 2nd I'll get 10 and for the 3rd 0.

So the solution has to be per System_Key (already try to put aggr(expression, System_Key), getting null for all System_Key).

Thanks a lot for the afford!! any other ideas?

Re: Count-if with max function

swuehl — Thu, 14 Nov 2013 18:48:48 GMT

Do you need to be selection sensitive? That means, for a given System_Key, do the latest X samples change depending on selections?

If not, you can probably assign '1' to the latest sample for each System_Key, '2' for the second latest etc.

using a sort desc and autonumber() [look into the two argument version] in your script.

Creating a new field that index the samples on a timeline from now to back in history.

Then simply filter your records like

=count({<SampleNumberIndex = {"<=30"}>} SampleNumber)

Re: Count-if with max function

Carlos_Reyes — Thu, 14 Nov 2013 18:49:31 GMT

I think it should work with AGGR. Also, if you upload a small sample app it'll be easier to help you.

However you may try this:

Create a straight table with System_Key as dimension and the next expression:

AGGR (

Count( { $< Sample_Number={ ">= $(=Max(Sample_Number)-30) " } > } DISTINCT Sample_Number)

,System_Key)

Re: Count-if with max function

jerem1234 — Thu, 14 Nov 2013 19:29:30 GMT

Maybe try this in a straight table with dimension System_Key and expression:

=if(aggr(max(SampleNumber),System_Key)>30, aggr(max(SampleNumber),System_Key) - 30, 0)

So if you have a 40 samples for a system, this will give you a value of 10. If you have 20 samples, this will give 0.

or you can try:

if(aggr(max(SampleNumber),System_Key)>30, aggr(max(SampleNumber),System_Key) - 30, aggr(max(SampleNumber),System_Key))

So if you have 40 samples for a system, this will give you a value of 10. If you have 20 samples, this will give 20.

Not sure which you want.

Hope this helps!

Re: Count-if with max function

mark_casselman — Thu, 14 Nov 2013 23:09:40 GMT

Yonatan,

I think I cracked it. After some trial and error if came up with this formula:

sum(aggr(if(SampleNumber > (max(total<System_Key> aggr(max(SampleNumber),System_Key))-30) , 1, 0 ),SampleNumber, System_Key))

I am not sure that this is the most efficient/simple solution, but I tested it and it did the job.

Some explaination:

aggr(max(SampleNumber),System_Key)

gives a highest samplenumber for every System_key

max(total<System_Key> aggr(max(SampleNumber),System_Key))

gives this highest number back for every individual sample (try both formulas in a straight table and you will see the difference)

if(SampleNumber > (max(total<System_Key> aggr(max(SampleNumber),System_Key))-30) , 1, 0 )

substract 30 and check every sample number is bigger or not. If bigger return 1 else return 0

aggr(if(SampleNumber > (max(total<System_Key> aggr(max(SampleNumber),System_Key))-30) , 1, 0 ),SampleNumber, System_Key)

Do the previous for every samplenumber of every system_key

sum(aggr(if(SampleNumber > (max(total<System_Key> aggr(max(SampleNumber),System_Key))-30) , 1, 0 ),SampleNumber, System_Key))

And sum it !

The result gives the total number of samples that exist and that are within the last 30.

It was quite a challenge !

Mark

Re: Count-if with max function

Sun, 17 Nov 2013 07:06:23 GMT

Hi,

To answering your question, I do need it to be selection sensitive. Therefore putting constant number there won't work. Thank you for your effort!

Re: Count-if with max function

Sun, 17 Nov 2013 07:12:26 GMT

Hi Mark,

Thanks a lot!!! Works perfectly!!

Also, thanks for the detailed explanation!!!

Thank you all for all of your ideas and attempts - Much appreciated

Re: Count-if with max function

mark_casselman — Sun, 17 Nov 2013 09:41:21 GMT

Hi Yonatan,

My pleasure.

Kindly label response as 'correct answer', this helps other users to find the correct answer.

Mark