Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Chart based on latest data

Hi,

Need to display the latest data based on date and other dimensions.

I need to show the latest stock available in the stores. Need to plot a chart with store in X axis and latest stock information(Based on the date) for that store in y axis.

Data:

Date    Store,  Product,    Stock

1-Jan,   A,  P1,   100

2-Jan,   A,  P1,  70

2-Jan, A,  P2,  20

1-Jan,  B,  P1,  20

1-Jan, B, P2,  10

My chart should like like below

Store,   Stock

A,  90

B,  30

Pls Suggest some solutions.

7 Replies
MVP
MVP

Re: Chart based on latest data

Hi

For best performance, I would calculate a flag field in script to mark the latest value by store:

T_MaxDates:

LOAD Max(Date) As MaxDate,

  Store

Resident Data

Group By Store;

Join(Data)

LOAD Store,

  MaxDate

Resident T_MaxDates;

Join(Date)

LOAD Store,

  Date,

  If(Date = MaxDate, 1, 0) As LastDateFlag

Resident Data;

DROP Table T_MaxDates;

Then your chart expressions become simple (and fast):

   =Sum({<LastDateFlag = {1}>} Stock)

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Chart based on latest data

Hi Jonathan,

Your solution works fine. But I have to find the latest date flag for many dimesional level. Is there any way to do it in chart expression itseslf?

Something like

firstsortedvalue(aggr(sum(Stock),Store,Date),-Date)...

Not applicable

Re: Chart based on latest data

take Date from store table store in variable

like vLatestDate =max(Date)

pass this in chart using set analysis

kiranmanoharrode
Contributor III

Re: Chart based on latest data

Dear Shanti,

Create 1 variable vMaxDate

= Max(Date)

make Bar Chart

Take Dimension as a Store

And try below Expression

=Sum({<Date={"$(vMaxDate)"}>}Stock)

Regards
Kiran Rode

Not applicable

Re: Chart based on latest data

We cannot use a variable as it will always show the maximum date of the stock available irrespective of the Store. For each store the maximum date will vary. So we have to do find out the maximum date for each store to get the latest stock information


er_mohit
Honored Contributor II

Re: Chart based on latest data

Try this

FirstSortedValue(Aggr(sum(Stock),DATE,Store),-Aggr(max(DATE),Store))

Not applicable

Re: Chart based on latest data

Hi Shanthi, You can't use the set analysis here because set analysis is calculated not row by row.

The Best method is create the script level suggested by Jonathan

Community Browser