Rank and Sum function with variables

[#1]

I am trying to create a table that dynamically updates to show the top 4 product Revenues based on a Salesperson selection.

I am using a variable so that the timeframe can be easily updated (called vmaxmonth)

I am having trouble with my rank and sum equations using the variable vmaxmonth

The issue seems to be related to the variable being contained within the rank function:

works:

  =sum({$<Year={2012},Month = {"<=$(=vmaxmonth)"},

  ProdFinal={"=rank(sum({$<Year={2012},Month={1,2}>}Net)

  ,4,1)=1"}>} Net)/vfactor

does not work :

=sum({$<Year={2012},Month = {"<=$(=vmaxmonth)"},

  ProdFinal={"=rank(sum({$<Year={2012},Month={"<=$(<=vmaxmonth)"}>}Net)

  ,4,1)=1"}>} Net)/vfactor

thanks in advance

[swuehl]

Try this:

=sum({$<Year={2012},Month = {"<=$(vmaxmonth)"},

  ProdFinal={"=rank(sum({$<Year={2012},Month={"<=$(vmaxmonth)"}>}Net)

  ,4,1)=1"}>} Net) / vfactor

[#3]

unfortunately this does not work. it doesnt seem to work with the "<=$(vmaxmonth)" within the rank function

[swuehl]

Ah, I now see that you are embedding double quotes in double quotes, I think that's the problem. Let me think...

[swuehl · Accepted solution]

Try creating a new variable vmaxmonthexp in variable overview:

=concat({<Month={"<=$(vmaxmonth)"}>} distinct Month,',')

then you could use this variable in the set element(s) instead of "<=$(vmaxmonth)":

=sum({$<Year={2012},Month = {$(vmaxmonthexp)},

  ProdFinal={"=rank(sum({$<Year={2012},Month={$(vmaxmonthexp)}>} Net)

  ,4,1)=1"}>} Net) / vfactor

Maybe I have missed something easier, but this should work.

Regards,

Stefan

[#6]

works, thank you!

[suvechha_b]

Can you please help me out ...

please check the below link..

http://community.qlik.com/message/411018#411018