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

    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

       

       

      thanks in advance