3 Replies Latest reply: Jul 16, 2015 5:21 PM by Manish Kachhia

# Max date of a specific type of value within set analysis

Hi,

I have this data set:

Type, Date, Result

Fruit, 2015-07-14, 0

Fruit, 2015-07-13, 0

Fruit, 2015-07-12, 1

Fruit, 2015-07-11, 0

Vegetable, 2015-07-15, 1

Vegetable, 2015-07-13, 0

Vegetable, 2015-07-12, 1

Vegetable, 2015-07-11, 0

Vegetable, 2015-07-14, 0

];

I want to count the number of times the Result is 1 for fruits and vegetables at the maximum date of each Type.

For example, the max date of fruit is 2015-07-14, so the number of times the value is 1 is zero.

but for vegetables, the maximum date is 2015-07-15 and there is a result on that date that is 1, so I should get a count of 1.

I'm setting these on in text objects, not charts with aggregate functions...

here's a sample file

• ###### Re: Max date of a specific type of value within set analysis

try

=sum({<Rule={Vegetable},Date={"\$(=date(max({\$<Rule={Vegetable}>} Date)))"}>} Result)

EDIT:

the expression above should be the same (if result is 0 or 1) as

=count({<Result={1},Rule={Vegetable},Date={"\$(=date(max({\$<Rule={Vegetable}>} Date)))"}>} Result)

other expression replace Vegetable with Fruit

• ###### Re: Max date of a specific type of value within set analysis

Perhaps

=FirstSortedValue({<Rule={'Vegetable'}>} Result,-Date)

• ###### Re: Max date of a specific type of value within set analysis

=Count({<Result = {1}, Rule = {'Vegetable'}, Date = {"\$(=Date(Max({<Rule = {'Vegetable'}>}Date)))"}>}Result)

and

=Count({<Result = {1}, Rule = {'Fruit'}, Date = {"\$(=Date(Max({<Rule = {'Fruit'}>}Date)))"}>}Result)