Expression

Tue, 11 Mar 2014 20:55:37 GMT

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.

topic Expression in QlikView

Expression