topic Re: Problem Max Function on Set Analysis in QlikView

Problem Max Function on Set Analysis

Anonymous — Thu, 14 May 2015 21:11:08 GMT

Hello Community, I'm having a problem that I hope you can help me with:

I have the following situation:

I want to count every ID when the product is Apple, the quantity is higher or iqual than 5 and the highest of the Dates that remains of the previous restrictions.

The table would be like this:

So far, I've tried the following sentence:

count({<Product={'Apple'}, quantity={">=5"}, Date=Max(Date)>} Distinct ID)

The expression should give as a result 2 ID's.

Which is the correct syntax for the Max Function? so far every other sentence works.

Thanks a lot!

Re: Problem Max Function on Set Analysis

dgreenberg — Thu, 14 May 2015 21:56:13 GMT

What is it currently returning?

Re: Problem Max Function on Set Analysis

Anonymous — Thu, 14 May 2015 22:02:24 GMT

The correct syntax for the max date will be
Date={"$(=date(max(Date)))"}

(Given the sample of the data, I don't see that the date condition is needed though)

Edit: After looking more at this, I think that the date condition will be harmful. You'll be better of without it. Or, I didn't understand the requirements right (?)

Re: Problem Max Function on Set Analysis

ToniKautto — Thu, 14 May 2015 22:58:31 GMT

I have an example of the syntax in Set expression and dollar expansion.

This example also show that the Max() aggregation is not evaluated on each row, which is important to keep in mind.

Re: Problem Max Function on Set Analysis

Anonymous — Fri, 15 May 2015 12:26:43 GMT

Still not working, the expression is not returning any value

Is there another way to do what I need to do without the max function?

Re: Problem Max Function on Set Analysis

sunny_talwar — Fri, 15 May 2015 12:47:50 GMT

In the example you have given the max date for apples is 12-31-2014 and on that date quantity is 4 so count is equaling 0. If I change your data a little bit, then this should give you the correct result.

SET DateFormat='MM-DD-YYYY';

Table:

LOAD * INLINE [

Product, Quantity, Date, ID

Apple, 9, 08-01-2014, 1

Apple, 4, 03-01-2014, 1

Orange, 44, 04-01-2014, 1

Orange, 44, 11-01-2014, 1

Orange, 44, 08-30-2014, 1

Orange, 44, 11-01-2014, 1

Apple, 4, 12-31-2014, 1

Orange, 44, 01-01-2013, 1

Apple, 7, 05-01-2015, 1

Apple, 7, 05-01-2015, 5

];

and using this expression:

=Count({<Product={'Apple'}, Quantity={">=5"}, Date={"$(=Date(Max({<Product = {'Apple'}>}Date), 'MM-DD-YYYY'))"}>} Distinct ID)

I get output 2 in a text box object.

Please find attached the qvw for reference.

HTH

Best,

Sunny