How to calculate a ratio like 2:1

robdmitchell · 2022-09-12T15:02:55+00:00

There was an error rendering this rich post.

Hello, been looking at this one and cannot see how to make the calculation in a ETL or Beast Mode.

What I am trying to achieve is a ratio or products sold, for example we sell 2 used and 1 new i want to display a ratio of 2:1.

Is this possible?

In my data i have the following available:

1) N/U = Text value of New or Used per product sold

2) New = if new then 1, all other values 0

3) Used = if new then 1, all other values 0

So i can get the total products sold, total new and total used sold but cannot seem to work out how to display it how i need/want it to display.

Any help will be appreciated.

Thanks

Rob

Find more posts tagged with

Accepted answers

MarkSnodgrass

You can use the formula tile and the concatenate function to build your ratio string like this:

CONCAT(`Usedfield`,':',`Newfield`)

This would build your string.

RobSomers

@robdmitchell Also to build on what @MarkSnodgrass posted, if you have unusual ratios (216:81), then you could do the following:

CONCAT(SUM('UsedField')/SUM('NewField'),' : ','1')

This would always give you the number of used for one new.

RobSomers

@robdmitchell Yes, use the ROUND() function on your division in the beast mode:

ROUND(SUM('U')/(SUM('N'),2)

All comments

MarkSnodgrass

You can use the formula tile and the concatenate function to build your ratio string like this:

CONCAT(`Usedfield`,':',`Newfield`)

This would build your string.

RobSomers

@robdmitchell Also to build on what @MarkSnodgrass posted, if you have unusual ratios (216:81), then you could do the following:

CONCAT(SUM('UsedField')/SUM('NewField'),' : ','1')

This would always give you the number of used for one new.

robdmitchell

Tip Top, this worked a treat. @RobSomers, have applied the formula in beast mode, is there a way i can reduce to just 2 decimal places, looks like 9.604651162790697:1

RobSomers

@robdmitchell Yes, use the ROUND() function on your division in the beast mode:

ROUND(SUM('U')/(SUM('N'),2)

robdmitchell

Legend, thanks for that, have a great rest of the week.