Order/Rank column with concatened values (considering only part of the string)

[therealdees]

Hello,

I've made a table chart that has a button that activates the frequency or the variance for the displayed data and it does that by concatenating both. The problem is that it loses the number formatting and I can't no longer order/rank the columns by clicking.

Is there a way I could point out to qlik sense to consider only what's outside the parenthesis, that is, the actual value?

I've attached a print of the table showing how the data is displayed. Unfortunately I can't add a sample as it contains sensitive data. If anyone could help me out or at least give me a hint on what to research, I'd be very thankful.

[Or]

You could use Dual(StringInterpretation, Number) rather than just having the string, which you could then sort numerically. Or you could just use two fields instead of concatenating.

[therealdees]

Hi! Thank you for you reply!

I just did some research and understood the concept and it does seems to potentially resolve my problem. I applied it in the chart script area, but although it apparently shows the correct ordering in the edit screen (it outputs only the values considered as number) when I test it on the analysis screen it does not behave as expected (while showing the complete string).

The expression I'm using is the following:

If(vAtivarAnoAnterior = 2 And vAtivarVariacao = 1 And GetSelectedCount(Ano) = 1,
//THEN
FaturamentoCY,
//ELSE
If(vAtivarAnoAnterior = 2 And vAtivarVariacao = 2 Or vAtivarAnoAnterior = 1 And vAtivarVariacao = 2,
//THEN
If(vAtivarParticipacao = 1,
Dual(Num(FaturamentoCY,'R$ #.##0')&' ('&Num((FaturamentoCY / FaturamentoPY)-1,'+#.##0,00%;-#.##0,00%')&')', FaturamentoCY ),
//ELSE
Dual(Num(FaturamentoCY,'R$ #.##0')&' ['&Num(FaturamentoCY / (Sum(Total{<[Tipo_Movimento] = {'Vendas'}, [Ano] = {"$(=Max(Ano))"}>} [Valor Total]) - Sum(Total{<[Tipo_Movimento] = {'Devolução'}, [Ano] = {"$(=Max(Ano))"}>} [Valor Total])),'#.##0,00%')&']', FaturamentoCY)),
//ELSE
Faturamento
)
)

What am I doing wrong? Technically it understands I want to sort by "FaturamentoCY", which stands for the "numerical part" of the string

[therealdees]

I think it's a bit hard to read (I'm new here, not sure if there's a way to format as a code in a post) so I'm adding a print of the expression in this reply

[therealdees]

I just realized it actually works when I add a new column with only the DUAL part of the code. For some reason the other conditions (probably the ifs) are affecting the result and even if the syntax is OK nothing happens (actually it doesnt even show the parenthesis data anymore)

Does anyone have a clue? I've been searching the internet for hours but still no success. I saw something that the DUAL function will assign a "ID" to the number, so if I try to add another dual condition (maybe the ifs in this case) it will not work. It's weird because it won't work even if I use DUAL only in first snippet.