Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends,
As I need your help in set analysis, as in the blow data I would like to show the last week Sales in straight table.
The Date is organized like the follow.
Year Week Sales
2010 52 100
2011 01 200
2011 02 500
.........
.......
2011 52 300
2012 01 400
.....................
.............................
2012 52 209
2013 01 225
Regards
Ammu
HI
Try likethis
=sum({<Week = {'$(vmax)'}>}Sales)
where vmax = Max(week)
Hello Mayil Vahanan.
Thanks for your reply, Okay its working, now the max week is 52, minimum is 1. so for next week the year is 2013 and the week is 1, how will the expression will work.
Could you please clarify...
Regards
Ammu
use below code
sum({<Year={$(=max(Year))},Week={$(=max({<Year={$(=Max(Year))}>}Week))}>}Sales)
it will calculate max week on the basis of Max Year
hope this helps
Hello Luminary Sahatwar,
Thanks for your reply, still I am unable to fix the issue, I have attached the qv.
Could you please check and update me.
Regards
Ammu
I have modified your script as
lOAD *, Year&Num(Week,'00') AS YearWeek Inline
[Year, Week, Sales
2012, 32, 434
2012, 33, 457
2012, 34, 480
2012, 35, 277
2012, 36, 251
2012, 37, 242
2012, 38, 243
2012, 39, 250
2012, 40, 228
2012, 41, 247
2012, 42, 239
2012, 43, 277
2012, 44, 298
2012, 45, 314
2012, 46, 349
2012, 47, 349
2012, 48, 358
2012, 49, 336
2012, 50, 321
2012, 51, 300
2012, 52, 306
2013, 1, 291
];
Then use expression as
If you have only one entry per week then use this
=FirstSortedValue({<YearWeek-={$(=Max(YearWeek))}>} Sales, -YearWeek)
If you have more than one entry per week the use this
=FirstSortedValue({<YearWeek-={$(=Max(YearWeek))}>}Aggr(Sum(Sales),YearWeek), -YearWeek)
Hope it helps
Celambarasan
Hello Celambarsan,
Thanks for your reply, your expression is working in test and loading through in line.
But when I try to retrived the data from DB, it still getting error like -. The previous expression which is working fine upto 52 Week, only the begining of 1st week I am getting such error, If I am in 2 Week, the old expression which is working fine.
=Sum({<WEEK={"$(=max({<YEAR={"$(=max(YEAR))"}>}WEEK-1))"}>}Sales)
can you tell me why its not working..
Regards
Ammu