Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Showing Values, but only Latest Date

Hello,

The following example illustrates my problem.

DateStockMutationStock
10-04-2013+1020
10-04-2013+1030
10-04-2013+1040
14-04-2013+4080
14-04-2013+40120

For my graphs I want to display the stock for each day. In table form my intention looks as follows:

DateStock
10-04-201340
14-04-2013120

How can I make an expression so only the latest value of a date is selected?

1 Solution

Accepted Solutions
er_mohit
Honored Contributor II

Re: Showing Values, but only Latest Date

Hiiii

For Expression side

try this one

Temp:

LOAD date(Date#(Dt,'DD-MM-YYYY'),'DD-MM-YYYY')AS DT,* INLINE [

    Dt, SM, Stk

    10-04-2013, +10, 20

    10-04-2013, +10, 30

    10-04-2013, +10, 40

    14-04-2013, +40, 80

    14-04-2013, +40, 120

];

take a dimension DT in pivot table and add expression

FirstSortedValue(aggr(max(Stk),SM),-aggr(DT,SM))   // For Stock

hope it helps you

8 Replies
Not applicable

Re: Showing Values, but only Latest Date

Hi

 

 

TimestampFormat

required to get max time on perticular date.

=M/D/YYYY h:mm:ss

Not applicable

Re: Showing Values, but only Latest Date

Hey,

Can you explain me how i can use this? Cause i dont understand. Right now im trying the following, with not good results. (used in chart expressionSmiley Happy

sum(aggr(FirstSortedValue(Stock, -Date),Date, Stock))

Not applicable

Re: Showing Values, but only Latest Date

I am trying this, and it starts to look more like it. But still alot of values are not visible.

er_mohit
Honored Contributor II

Re: Showing Values, but only Latest Date

Try this in script

AB:

LOAD date(Date#(DAte,'DD-MM-YYYY'),'DD-MM-YYYY')AS date,* INLINE [

    DAte, StockMutation, Stock

    10-04-2013, +10, 20

    10-04-2013, +10, 30

    10-04-2013, +10, 40

    14-04-2013, +40, 80

    14-04-2013, +40, 120

];

ABC:

LOAD max(Stock) as Lateststock,date

Resident AB

Group by  date;

and in pivot table take date as dimension

and in expression write Lateststock

hope it helps you

Not applicable

Re: Showing Values, but only Latest Date

Thank you, i feel like im almost there.

The problem is, i dont want to sort on value, but really on date. so the latest value in the list. The value can also be lower than the first value.

Im sorry i didnt state this clear in the beginning

er_mohit
Honored Contributor II

Re: Showing Values, but only Latest Date

ok

then try this one

LOAD StockMutation,

max(Stock)as vmax,

FirstSortedValue(date(date),-Stock)as LTV

Group by StockMutation;

AB:

LOAD date(Date#(DAte,'DD-MM-YYYY'),'DD-MM-YYYY')AS date,* INLINE [

    DAte, StockMutation, Stock

    10-04-2013, +10, 20

    10-04-2013, +10, 30

    10-04-2013, +10, 40

    14-04-2013, +40, 80

    14-04-2013, +40, 120

];

Not applicable

Re: Showing Values, but only Latest Date

And how would the expression look like?

er_mohit
Honored Contributor II

Re: Showing Values, but only Latest Date

Hiiii

For Expression side

try this one

Temp:

LOAD date(Date#(Dt,'DD-MM-YYYY'),'DD-MM-YYYY')AS DT,* INLINE [

    Dt, SM, Stk

    10-04-2013, +10, 20

    10-04-2013, +10, 30

    10-04-2013, +10, 40

    14-04-2013, +40, 80

    14-04-2013, +40, 120

];

take a dimension DT in pivot table and add expression

FirstSortedValue(aggr(max(Stk),SM),-aggr(DT,SM))   // For Stock

hope it helps you

Community Browser