Formatting of a custom ratio

[davehutchinson]

Hi all, I'm having trouble formatting 2 sets of numbers that are put together as a ratio.

I've been asked to calculate a ratio of 2 groups, however I've been told to display the 2 numbers out of 100.

I've calculated the 2 numbers separately, but when I put them together in the format, Qlik doesn't seem to notice my NUM() formatting and keeps all the decimal places.

The format I need the ratio to be in is XX:XX e.g. 50:50 or 10:90 

(yes I know that's not normally how ratios work but I've been arguing with a brick wall over that one!)

So I've calculated the first number in its own KPI visual and got the first number in the right format of "89":

(NUM(ROUND(SUM({<[Category]={"TLR SUBCON"},[Cat2]={"INDIA"}>}[TOTAL]))/ROUND(SUM({<[Category]={"TLR SUBCON"}>}[TOTAL])),'##'))*100

And I've got the second number using the same method with the result of "11":

(NUM(ROUND(SUM({<[Category]={"TLR SUBCON"},[Cat2]={"UNITED KINGDOM"}>}[TOTAL]))/ROUND(SUM({<[Category]={"TLR SUBCON"}>}[TOTAL])),'##'))*100

So now I need to put them together to get the ratio of "89:11".  So I've used:

=(NUM(ROUND(SUM({<[Category]={"TLR SUBCON"},[Cat2]={"INDIA"}>}[TOTAL]))/ROUND(SUM({<[Category]={"TLR SUBCON"}>}[TOTAL])),'##'))*100
& ':' &
(NUM(ROUND(SUM({<[Category]={"TLR SUBCON"},[Cat2]={"UNITED KINGDOM"}>}[TOTAL]))/ROUND(SUM({<[Category]={"TLR SUBCON"}>}[TOTAL])),'##'))*100

However this is appearing is 88.746596075938:11.253430924062

For some reason my "NUM" formatting isn't being recognised. Can anyone help me format the 2 sets of numbers?

Thanks

[daturpin]

I've had similar problems and got around them by storing intermediate calculations as variables

Edit sheet and click on variables in the left lower corner

create a variable like "numerator" and set the Definition as =(NUM(ROUND(SUM({<[Category]={"TLR SUBCON"},[Cat2]={"INDIA"}>}[TOTAL]))/ROUND(SUM({<[Category]={"TLR SUBCON"}>}[TOTAL])),'##'))*100

create another cariable like "denominator" and set the Definition as = (NUM(ROUND(SUM({<[Category]={"TLR SUBCON"},[Cat2]={"UNITED KINGDOM"}>}[TOTAL]))/ROUND(SUM({<[Category]={"TLR SUBCON"}>}[TOTAL])),'##'))*100

Then in your visual, concat with the function =$(numerator) & ":" & $(denominator)

[davehutchinson]

Thanks, but that's giving me the same result 😭

[daturpin]

Okay, I tried it out by creating a variable with the Definition which is basically in the same format as yours:

=(NUM(ROUND(123.42344234)/ROUND(676.2342344),'##'))*100

And it returns 18.1952...

Then I tried moving the percentage inside the NUM():

=(NUM(ROUND(123.42344234)/ROUND(676.2342344)*100,'##'))

And it returns 18

Try that minor change in the expression. And if that solves it, then there is actually no reason to store the variables unless you like to simplify complex expressions.

[davehutchinson]

Thanks, I'm still getting a million decimals though.  

I've managed to find a bit of a work around.  not ideal but it will do.

If I create the ratio using 2 KPI visuals and 1 text visual I can basically create the ratio manually.  not idea but does the job.  Cheers