Problem Max Function on Set Analysis

[Anonymous]

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!

[dgreenberg]

What is it currently returning?

[Anonymous]

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 (?)

[ToniKautto]

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.

[Anonymous]

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?

[sunny_talwar]

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