Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Team/
i want to get the weekly max date value in table by using set analysis. like
each week have 4-5 working days. on each week i have to identify the max date of that week and get the value on that week.
below is the screenshot
Expression: Sum({<[Trading Date]={'$(=max([Trading Date]))'}>} [Close Rate])
Try using AGGR
=AGGR(Sum({<[Trading Date]={'$(=max([Trading Date]))'}>} [Close Rate]),Weekno)
HTH
sushil
Bifurcate date into week as you do for month,year,q1 etc......one you get the week part which contain w1,w2,w3,w4
then use set analysis as u did, just in place of Trading date mention your week field like., Week=max(week)
That is also not working i used join for that. while loading putting a flag value on each max date of particular record.
Hi Jitendra,
Not sure how you can achieve this in expression, but in script you can do this.
X:
LOAD
max(Date) as Date,
[Week no] as weekno
FROM
............ group by [Week no];
left join(X)
load
Date,
sales
FROM
......
Yes,
i had already done in that way only
Hi
First Create week Numbers using Week() Function.
After this, Create 2 Variables:
1. vMaxWeek
=Max(Week_NewDate)
2. vMaxDate
=Max(NewDate)
Call these 2 Variables into your Expression:
=SUM({<Week_NewDate={'$(vMaxWeek)'},NewDate={'$(vMaxDate)'}>}Amount)
After doing this, you'll get the desired Result.
Also, go through the Attachment.
Regards
Av7eN
sum({<[Trading Date]= $(vWeek >}[Close Rate])
vWeek = max(TradingDate).
.This expression worked for me with my own sample sample data .
Hope you might get some idea .
Thanks
That is not working in my case because i have more dimension to use in my table
That is not working in my case because i have more dimension to use in my table