Hi all, I am having a heck of a time getting one of my variables to properly evaluate. I thought I had it figured out, but I'm stumped.
I defined a common set expression which would take a commodity type (Gas or ELectric), and then calculate the total volume of the commodity for the year.
These are defined in my load script:
SET v_VolumeSetExpression = "Service={$1},Concept={'Stores'}, Volume={'>=0'}";
SET v_VolumeForYear = "sum({<$(v_VolumeSetExpression($1)), Year={$2},[Volume UOM]={$3}>} Volume*[Volume Factor])";
This works great, and if I call them from a KPI or Table widget with the expression
=$(v_VolumeForYear('Gas', 2017)) (or any other year), it works as expected.
Now, I'm also trying to calculate the change year-over-year.
I thought that this expression would work:
SET v_VolumeEvolution = "($(v_VolumeForYear($1, $2, 'kWh'))) / ($(v_VolumeForYear($1, $2-1), 'kWh')))";
With the expectation I could also call this the same way
=$(v_VolumeEvolution('Gas', 2017)
I've tried all sorts of combinations and really thought my latest version would work:
SET v_VolumeEvolution = "($(v_VolumeForYear($1, $2, 'kWh'))) / ($(v_VolumeForYear($1, Num#($2)-1, 'kWh')))";
In this case, though, the variable editor shows the "Num#(" phrase repeated 10,000 (actually that number) times in the expanded expression. CLearly there's some sort of recursion going on but I'm absolutely stumped.
Can anyone help? I've been plugging away for literally hours and could use an expert pair of eyes on this.
Thanks in advance!
