Hi I'm using <GD.CN>. I am working on creating a m...
# gooddata-cna
Alex Gertman
10/06/2023, 4:39 PMHi I'm using GD.CN. I am working on creating a metric. I'd like to do something similar to "RUNSUM" but "RUNPRODUCT", i.e. a multiplication of all the values preceeding. Is there a function for that?
Alex Gertman
10/06/2023, 5:17 PMHere's the example:
(this is the data in my warehouse)
Month Return End Date VALUE_A
1 6/29/2018 -0.2348389005
2 7/31/2018 0.6384624014
3 8/31/2018 0.2733126972
4 9/28/2018 -0.1855167868
5 10/31/2018 -0.6838791671
6 11/30/2018 -0.6894894414
7 12/31/2018 -0.0080659472
8 1/31/2019 1.5209570212
I add another column/ metric which is SELECT (1+ VALUE_A/100) .
I want to do another metric that does a running product of my caclulated column
Month Return End Date VALUE_A =(1+VALUE_A/100) = RUNPRODUCT
1 6/29/2018 -0.2348389005 0.997651611 -0.2348389005
2 7/31/2018 0.6384624014 1.006384624 0.4021241428
3 8/31/2018 0.2733126972 1.002733127 0.6765358964
4 9/28/2018 -0.1855167868 0.998144832 0.4897640219
5 10/31/2018 -0.6838791671 0.993161208 -0.1974645393
6 11/30/2018 -0.6894894414 0.993105106 -0.8855924836
Alex Gertman
10/06/2023, 5:17 PMany advice?
j
Joseph Heun
10/09/2023, 10:36 AMHi Alex,
Are you trying to build this metric based off of values from what is calculated in Value_A? We cannot build metrics based on changing dynamic values in a report, but you can still use the arithmetic logic to build your report using the running totals.
j
Jakub Sterba
10/09/2023, 2:05 PMI think you can add little bit of math to your metric to solve it.
log(a*b*c)=log(a)+log(b)+log(c)select power(10,runsum(log(metric/x)))Jakub Sterba
10/09/2023, 2:08 PMScreenshot 2023-10-09 at 16.07.51.png
Jakub Sterba
10/09/2023, 2:29 PMor
SELECT EXP(RUNSUM(LN(metric/x)))a
Alex Gertman
10/09/2023, 3:15 PMthanks @Jakub Sterba and @Joseph Heun! I had not thought of using logs to do the math, that will do the trick, thank you!