Max date of a specific type of value within set analysis

[sibrulotte]

Hi,

I have this data set:

LOAD * INLINE [

    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

[maxgro · Accepted solution]

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

[antoniotiman]

Perhaps

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

[MK_QSL]

=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)