Hi ,
I am trying to accomplish a complex expression either through Set Analysis or Sum If:
Currently the expression looks like below:
=sum(if
(((Year='FY2013' or Year='FY2014' or Year='FY2015') and
[SUB GBU]='Hardware' and ([Fiscal Week]=Aggr(Nodistinct Max([Fiscal Week]),Quarter_Year))),Qty,0))
/
sum(Total<Quarter_Year>(if(((Year='FY2013' or Year='FY2014' or Year='FY2015') and
[SUB GBU]='Hardware' and ([Fiscal Week]=Aggr(Nodistinct Max([Fiscal Week]),Quarter_Year))),Qty,0)))
This is my expression , its actually creating a stack chart with Quarter and Products as dimensions. Question is :
- How do I limit my number of Products limited to top 5 apart from dimension limit?
I tried something below:, but not working:
if(Aggr(Rank(sum({1}Total<Product> Qty)),Product)<=5,Product)
2. How do I convert the entire expression into set analysys>
3. I dont want to hard code the Year, I want current plus previous year in the data?
Im not able to do this for long time, any help would be appreciated.
