1 Reply Latest reply on Sep 28, 2018 8:34 PM by swaroop.gantela

# Calculate RATIO using COUNT & COUNTD by Fixed Customer ID

Hi All,

I am struggling to make a calculation as pic below.

Basically I need to sum all Successful transactions and COUNTD (Unique Failed) and get a ratio but based on the Customer ID as fixed.

As per example below: Successful (59) / (Successful 59 + Countd Unique Failed 4) = 93% RATIO of Unique Failed Reason. Furthermore, I'll need to include some rows & filters. (by country and add in a timeline).

Right now I am using the following (crashed formulas):

1) IF [Tx State] = 'SUCCESSFUL' THEN [Tx INFO] END

2) IF [Tx State] != 'SUCCESSFUL' THEN [Tx INFO] END

3) {FIXED [Account ID]: COUNTD([COUNTD Failed])}

4) {FIXED [Account ID]: COUNT([COUNT Successful])}

5) ([Fixed Successful]) / ([Fixed Successful] + [Fixed Failed] )

Im getting the following result:

Hope can get some insights.

Thanks Community

Day of Tx Created Date & Time Month of Tx Created Date & Time Year of Tx Created Date & Time Unique Failed RATIO31 October 2017 0.91620111731 August 2018 0.93939393931 July 2018 0.7531 March 2018 0.430 November 2017 0.82608695730 October 2017 0.93939393930 September 2017 0.94871794930 September 2017 0.97761194030 August 2018 0.93939393930 May 2018 0.73333333330 April 2018 0.93939393929 November 2017 0.77777777829 July 2018 0.96551724129 June 2018 0.88235294129 June 2018 0.96551724129 March 2018 0.91620111728 November 2017 0.77777777828 August 2018 128 July 2018 0.88235294128 April 2018 0.88235294128 February 2018 0.91620111728 February 2018 0.97761194027 October 2017 0.91620111727 October 2017 0.96969697027 September 2018 0.91620111727 September 2018 127 September 2018 127 September 2017 0.91620111727 March 2018 0.73333333327 March 2018 0.91620111727 February 2018 0.91620111727 February 2018 0.97761194026 December 2017 0.93939393926 November 2017 0.826 November 2017 0.91620111726 November 2017 0.94871794926 November 2017 0.97761194026 October 2017 0.91620111726 October 2017 0.96969697026 September 2018 0.91620111726 September 2017 0.91620111726 September 2017 0.93939393926 August 2018 0.96551724126 June 2018 0.91620111726 May 2018 0.88235294126 April 2018 0.91620111726 February 2018 0.73333333326 February 2018 0.97761194026 January 2018 0.91620111725 December 2017 0.91620111725 December 2017 0.93939393925 October 2017 0.91620111725 October 2017 0.96551724125 October 2017 0.96969697025 September 2018 0.91620111725 September 2018 0.94871794925 June 2018 0.91620111725 April 2018 0.96551724125 March 2018 0.91620111724 December 2017 0.93939393924 November 2017 0.91620111724 October 2017 0.93939393924 September 2018 0.91620111724 September 2018 0.93548387124 August 2018 0.96551724124 July 2018 124 May 2018 0.92857142924 May 2018 124 April 2018 0.91620111724 April 2018 124 February 2018 0.97761194023 December 2017 0.91620111723 October 2017 0.91620111723 August 2018 0.96551724123 June 2018 0.94871794923 May 2018 0.91620111723 April 2018 0.91620111723 March 2018 0.91620111723 February 2018 0.91620111723 January 2018 0.91620111722 December 2017 122 September 2018 0.88235294122 August 2018 0.96551724122 July 2018 0.93939393922 June 2018 0.96551724122 May 2018 0.91620111722 May 2018 0.92857142922 April 2018 0.96551724122 March 2018 0.91620111722 March 2018 0.97761194022 February 2018 0.73333333322 February 2018 0.86666666722 February 2018 0.91620111722 February 2018 0.97761194022 January 2018 0.93939393922 January 2018 0.97761194021 September 2018 0.91620111721 June 2018 0.91620111721 May 2018 0.96551724121 May 2018 121 March 2018 0.97761194021 February 2018 0.91620111721 February 2018 0.97761194020 November 2017 0.93939393920 September 2017 0.93939393920 August 2018 0.820 August 2018 0.96551724120 June 2018 0.91620111720 May 2018 0.92857142920 May 2018 0.94871794920 April 2018 0.93939393920 March 2018 0.97761194019 December 2017 0.82608695719 December 2017 0.91620111719 August 2018 0.92857142919 April 2018 0.88235294119 April 2018 0.96551724119 February 2018 0.96551724119 February 2018 0.97761194019 February 2018 118 November 2017 0.818 November 2017 0.93939393918 October 2017 0.91620111718 October 2017 0.93939393918 September 2018 0.93548387118 August 2018 0.88235294118 July 2018 118 June 2018 0.91620111718 June 2018 0.93939393918 May 2018 018 March 2018 0.92857142918 March 2018 0.97761194018 February 2018 0.91620111718 February 2018 0.96551724118 February 2018 0.97761194018 February 2018 117 December 2017 0.93939393917 November 2017 017 September 2018 0.93548387117 September 2017 0.94871794917 August 2018 0.93548387117 July 2018 0.92857142917 July 2018 117 May 2018 0.92857142917 April 2018 0.91620111717 March 2018 0.94871794917 February 2018 0.97761194016 December 2017 0.82608695716 October 2017 0.86666666716 October 2017 0.91620111716 September 2018 0.73333333316 September 2018 0.93548387116 September 2017 0.94871794916 August 2018 0.92857142916 July 2018 0.94871794916 July 2018 116 May 2018 0.91620111716 April 2018 0.88235294116 April 2018 0.96551724116 February 2018 0.97761194016 January 2018 0.86666666715 December 2017 0.77777777815 December 2017 0.82608695715 December 2017 0.86666666715 December 2017 0.93939393915 December 2017 0.97761194015 November 2017 0.93939393915 August 2018 0.94871794915 August 2018 115 May 2018 0.91620111715 May 2018 0.94871794915 April 2018 0.91620111715 March 2018 0.86666666715 March 2018 0.97761194015 February 2018 0.86666666715 February 2018 0.91620111715 February 2018 0.93939393915 February 2018 0.97761194014 December 2017 0.93939393914 November 2017 0.93939393914 October 2017 014 September 2018 0.88235294114 August 2018 114 July 2018 0.94871794914 June 2018 0.93939393914 June 2018 0.96551724114 April 2018 0.91620111714 March 2018 0.93939393914 March 2018 0.94871794914 March 2018 0.96551724114 March 2018 0.97761194014 February 2018 0.97761194013 December 2017 0.82608695713 December 2017 0.93939393913 November 2017 0.93939393913 October 2017 0.91620111713 September 2018 0.93548387113 September 2018 0.93939393913 September 2017 0.94871794913 July 2018 0.93939393913 June 2018 0.91620111713 May 2018 0.93939393913 March 2018 0.73333333313 March 2018 0.97761194013 February 2018 0.96551724113 February 2018 0.97761194013 January 2018 0.94871794912 December 2017 0.77777777812 November 2017 0.97761194012 October 2017 0.91620111712 October 2017 0.97761194012 September 2018 0.91620111712 September 2018 0.93548387112 September 2018 112 September 2017 0.91620111712 August 2018 0.93548387112 June 2018 0.88235294112 June 2018 0.91620111712 May 2018 0.91620111712 May 2018 0.92857142912 May 2018 0.94871794912 April 2018 0.91620111712 March 2018 0.96551724112 March 2018 0.97761194012 February 2018 0.93939393912 January 2018 0.91620111712 January 2018 0.93939393911 November 2017 0.93939393911 October 2017 0.93939393911 October 2017 0.94871794911 September 2018 0.93548387111 June 2018 0.91620111711 June 2018 111 May 2018 0.73333333311 May 2018 0.91620111711 March 2018 0.93939393911 March 2018 0.94871794911 March 2018 0.97761194010 November 2017 0.610 November 2017 0.94871794910 October 2017 0.94871794910 September 2018 0.93548387110 September 2017 0.93939393910 September 2017 110 August 2018 0.93548387110 May 2018 0.94871794910 April 2018 0.93939393910 April 2018 0.96551724110 April 2018 110 March 2018 0.73333333310 March 2018 0.94871794910 March 2018 0.97761194010 February 2018 0.94871794910 January 2018 0.86666666710 January 2018 0.91620111710 January 2018 0.9393939399 December 2017 0.9776119409 November 2017 0.8260869579 October 2017 0.9487179499 September 2018 0.69 September 2018 0.9162011179 September 2018 0.9354838719 September 2017 19 April 2018 0.69 April 2018 0.8666666679 April 2018 0.8823529419 March 2018 0.9776119409 February 2018 0.9487179499 January 2018 0.9393939399 January 2018 0.9487179498 December 2017 0.9393939398 December 2017 0.9776119408 September 2018 0.8666666678 September 2018 0.8823529418 August 2018 0.9354838718 August 2018 18 June 2018 0.8666666678 May 2018 0.7333333338 May 2018 0.9285714298 April 2018 0.8823529418 April 2018 0.9393939398 March 2018 0.8666666678 March 2018 0.9393939398 March 2018 0.9776119408 January 2018 0.9393939397 November 2017 0.8260869577 November 2017 0.9393939397 October 2017 0.9776119407 August 2018 0.9354838717 June 2018 0.9162011177 May 2018 0.9487179497 March 2018 0.9776119407 February 2018 0.9776119407 January 2018 0.9162011177 January 2018 16 December 2017 0.9487179496 November 2017 0.8260869576 November 2017 0.9162011176 September 2017 0.9487179496 August 2018 0.9393939396 April 2018 0.9655172416 March 2018 0.9162011176 March 2018 0.9655172416 March 2018 0.9776119406 February 2018 0.9776119406 January 2018 0.9487179495 October 2017 0.9393939395 September 2017 0.9487179495 August 2018 0.755 June 2018 0.55 May 2018 0.755 May 2018 0.9487179495 March 2018 0.9162011175 March 2018 0.9776119405 February 2018 0.9776119405 January 2018 0.9162011175 January 2018 0.9393939395 January 2018 14 December 2017 04 December 2017 0.9487179494 November 2017 0.8260869574 October 2017 04 September 2017 14 August 2018 0.8823529414 May 2018 0.9655172414 March 2018 0.9776119404 February 2018 0.7333333334 January 2018 0.9162011174 January 2018 0.9393939393 December 2017 0.9393939393 December 2017 0.9487179492 November 2017 0.7777777782 November 2017 0.8260869572 October 2017 0.9162011172 October 2017 0.9487179492 September 2017 0.9487179492 July 2018 0.7333333332 July 2018 0.9393939392 July 2018 0.9655172412 June 2018 0.9162011172 June 2018 0.9285714292 May 2018 0.9162011172 May 2018 0.9393939392 April 2018 0.9162011172 March 2018 0.9162011172 March 2018 0.9776119402 March 2018 12 January 2018 0.9162011171 December 2017 0.8260869571 December 2017 0.9162011171 October 2017 0.7777777781 October 2017 0.9162011171 October 2017 0.9487179491 September 2018 0.51 September 2017 0.9487179491 August 2018 0.9655172411 June 2018 11 February 2018 0.977611940

• ###### 1. Re: Calculate RATIO using COUNT & COUNTD by Fixed Customer ID

Diego,

First wanted to get clarification.

Is the 91.6% shown in the tooltip of your screenshot the expected result?

I very likely missed it, but I wasn't seeing where you were summing the Successful amount.

Should #4 be:

4) {FIXED [Account ID]: SUM([COUNT Successful])}  ?