I am trying to find an expression that will show in a pivot the inventories per month and per year.
The year is totaling the months and summarizes them, something i do not want! I need to get the value of every last month (i.e. December) when totaling the year.
If you want to enter more deep, please check attached.
In my dataset I have for every site the Bikes_EOM. Now my expression is sum(Bikes_EOM).
Per month it is correct, but per year not!
I have no problem to show also zero values for missing months or anything else. I just need to get the correct result.
Moreover I am thinking to find an alternative, i.e. showing only december in a new column and find a way to hide/show when expanding/colapsing.
Pivot tables don't allow you to choose how to aggregate. You can partially sum based on your dimensions but that is as far as it goes.
However, if you create a straight table there is a trick. Instead of selecting not totals or expression total, select last string of rows. Since December will alwsys be your last value you can do this and december will show us.
Now, with regards to getting a pivot view without the collapsing capability. This is messy, but its the only way. Each expression needs to be for a specific year for that month. So you can have year running across horizontally as you will have a set analysis expression for each year or month depending on what you want to do. I would recommend not doing it by month and this is because you need the last string value as per your total.
There is no clean way the way I see it, getting the output you require
The expression should be conditional, based on dimensionality, so expression for total is different. and, expression for total should take only December in consideration, for example sum({<Month={dec}>} Inventory).
