Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sibrulotte
Contributor III

Max date of a specific type of value within set analysis

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

1 Solution

Accepted Solutions
MVP
MVP

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

3 Replies
MVP
MVP

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

antoniotiman
Honored Contributor III

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)

Community Browser