I'm trying to create an expression that would show spot inventory like in the below table. The spot inventory is a sum of a given line value and all the values above it within a year, next year it starts over and show only the given line value.
So for an inventory type of 'Change', it only links to the current month, so you get only the current Change value. For an inventory type of 'Spot', it links to the current month and all previous months, so you get the sum of all Change values up to that month of the year.
You can do this while loading the script itself. Sort the table on Year and Month (if you have date then sort it on date). Then add another feild which keeps on adding the previous records value only if the Years of current an previous records are same. this way you can directly use the new feild in your table
eg: if(Year=previous(Year), Previous(NewField)+Change) as NewField
Syed's solution is simpler. I'd go with that. A couple slight revisions are required, I think. First, I think you need to use peek() to check the value of a field that isn't in your input table. Second, need to add an "else" to pick up the Change value in January:
if(Year=previous(Year), peek(NewField)+Change, Change) as NewField
przemek wrote: Than you, John. It seems to be a solution but I was hoping for something using above() function or similar. Do you have an idea how to do it this way?<div></div>
Sure. Second expression, labeled 'Spot Inventory', this definition:
rangesum(Change,above("Spot Inventory"))
But I wouldn't do it that way. If you then select a single month, for instance, it will set the spot inventory to only that month's change value, because the above() is looking at your chart, not at your data. You really want it to look at data.
