Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community..
Here I have a requirement where I need to calculate the % change with a base month amount to the current dimension value amount.
In the pivot table.
where month and regions are a dimensions. 'Parts Inventory' and 'Parts % change' are the metrics.
'Parts % change': this can be calculated as the % change of amount over the month to the starting month value. Here the Aug-14 is the starting period.
***Here Aug-14 Parts amount is a base amount to consider for the period Aug-14 to Aug-15
for ex: for Feb-15= ( (Parts Inventory of Feb-15)-(Parts Inventory of Aug-15))/ (Parts Inventory of Aug-15) similar calculation for all months up to Aug-15.
I have created a static variable for the Aug-14 month amount. but when the dimension is month this variable showing value for only Aug. Not showing for other months because of this I could not able to get the % numbers in the table..
Please find the UI Table image attached ..
Please suggest the solutions
Regards
Suman
Since Aug-14 is the first value in the month dimension in your pivot table you can use something like First( sum(Parts)) to get the value of sum(Parts) of that first dimension value. The expression to calculate the % change would then be Sum(Parts) / First(Sum(Parts)) -1