Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try this:
=sum({$<Year={2012},Month = {"<=$(vmaxmonth)"},
ProdFinal={"=rank(sum({$<Year={2012},Month={"<=$(vmaxmonth)"}>}Net)
,4,1)=1"}>} Net) / vfactor
unfortunately this does not work. it doesnt seem to work with the "<=$(vmaxmonth)" within the rank function
Ah, I now see that you are embedding double quotes in double quotes, I think that's the problem. Let me think...
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
works, thank you!
Can you please help me out ...
please check the below link..