topic Set Analysis in QlikView

Set Analysis

Thu, 30 Jun 2011 11:53:51 GMT

Good evening everyone,

I have a dimention called Prod and the expression as in the below

=Count(if([aged]='2/1-30 DAYS',[Invoice Number])) +count(if([aged]='3/31-60 DAYS',[Invoice Number]))+count(if([aged]='4/61-90 DAYS',[Invoice Number]))+count(if([aged]='5/90+DAYS',[Invoice Number])).

I am getting the product and exact count of invoice numbers my question is I would like to display the first Highest of Prod and the count of invoice.

I tried set analysis but I failed to do that

Can any one have any idea to do this

Regards

Joe

Set Analysis

kaushiknsolanki — Thu, 30 Jun 2011 12:01:41 GMT

Hi,

If your expression gives you the correct invoice count.

Then to get the top(Highest) Prod with respect to count of invoice all you have to do is go to properties. in sort tab.

Select the expression and select numberic and descending.

This will give you top Prod.

Hope this is what you want.

Regards,

Kaushik Solanki

Set Analysis

Thu, 30 Jun 2011 12:09:04 GMT

Dear Kaushik,

Thanks for your reply Kaushik

But I would like to display the value only one who got the highest, can you wiite a expression using set analysis ...[Rank].

I tried to display it but not able to make it.

Thanks

Joe

Set Analysis

kaushiknsolanki — Thu, 30 Jun 2011 12:15:26 GMT

Hi,

If you want only one record which have highest count, then

Conver your chart into straight table.

Then go to properties.

Presentation tab.

You will see an option called. Max Number.

Assign value as 1.

And see the difference.

Regards,

Kaushik Solanki

Set Analysis

Thu, 30 Jun 2011 12:23:46 GMT

Dear Kaushik,

Thanks Kaushik it works....

but is it possible to control is through expression.

like the below I tried no reslut in a straight table

=if(aggr({1}rank(Count(Invoice Number)),[Prod]) = 1,Count (Invoice Number))

Regards

Joe

Set Analysis

Oleg_Troyansky — Thu, 30 Jun 2011 12:36:13 GMT

A couple of comments here...

First, I'd highly recommend replacing the IF functions by Set Analysis conditions, or separate counter fields for each bucket. So many IF statements must affect performance...

Second, you are summarizing several count() functions. In certain cases, if some of the data is missing, one of the count() functions might return a null(), which will render the whole expression as null(). This problem is especially common on 64-bit platform. To avoid it, use rangesum() with multiple aggregation functions:

Rangesum( count(...), count(...), count(...))

Finally, about your question - can you limit your Products to the top one, based on an expression, using set analysis. Yes, this can be done via using search syntax for set analysis. Let's use <expr> to signify your formula. The full expression would look like this:

Count( {<Prod={"=rank(<expr>)=1"}>} <field>)

this expression will produce the count of the field, only for the top product based on the rank of the formula that we called <expr>.

Cheers,

Set Analysis

kaushiknsolanki — Thu, 30 Jun 2011 12:36:49 GMT

Hi,

I dont know but i will suggest you to use simple and effective expression as its easy to find the problem.

Regards,

Kaushik Solanki

Set Analysis

Thu, 30 Jun 2011 13:31:57 GMT

Thanks a lot Kaushik....