Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Perhaps
=FirstSortedValue({<Rule={'Vegetable'}>} Result,-Date)
=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)