6 Replies Latest reply: Oct 24, 2013 3:55 AM by Suvechha Bhadra

# Rank and Sum function with variables

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

• ###### Rank and Sum function with variables

Try this:

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

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

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

• ###### Rank and Sum function with variables

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

• ###### Rank and Sum function with variables

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

• ###### Rank and Sum function with variables

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