Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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)...
take Date from store table store in variable
like vLatestDate =max(Date)
pass this in chart using set analysis
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
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
Try this
FirstSortedValue(Aggr(sum(Stock),DATE,Store),-Aggr(max(DATE),Store))
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