Growth Rate
I am looking to calculate Growth Rate in a Pivot Table in Sisense. The inability to use Lag in a Custom SQL statement is causing me to hit a road block (https://stackoverflow.com/questions/17340770/sql-how-to-calculate-percentage-change-growth).
I have tried doing the calculation both with a self join and inside the Pivot Tables using some of the built in functions, but these are not giving me the expected amount (I am not sure what calculations the built in Growth Rate functions are doing).
Below is what I am trying to do- For 10/2003: the new column should populate with the result of 0.9915*1.0723 then the result for 11/2003 should be 1.0189*(0.9915*1.0723) <- the previous months result, then 12/2003 would be 1.0136*(1.0189*(0.9915*1.0723)). I need the formulas to build off of the previous months result.
I've already seen this post which is not what I am looking to do: https://support.sisense.com/hc/en-us/community/posts/115000502347-Comparing-Two-Rows-in-Elasticube?input_string=Lag%20Function%20in%20Sisense
-
Hi Jessica,
I believe you'll be able to utilise this plugin which simplifies pivot API calls.
Then have a go at this script available here.
That script if for a running sum, but you will probably be able to modify the script so it give you the running product and not the running sum.
If you're ok with calculating this in the EC, have a look at this article.
Also there, you will need to manipulate the SQL query to provide a running product and not sum.
Let me know if you'd like to further discuss,
-
Thanks Ido!
I was initially trying to do the running product calculation but I am noticing the query which is similar to this one (https://support.sisense.com/hc/en-us/articles/360007490013-Calculate-Running-Sum) is dropping Months from my first Month column (October 2003 and January 2004) I noticed immediately. The InvestorName part of the query isn't messing this up because it is only filtered for one Investor so I am not sure why it is dropping certain months.
Here is what the table looks like without any self join
Please sign in to leave a comment.
Comments
2 comments