spaces in data is throwing me off

[becki_kain]

I have data coming in with "_" in the name, like P_201709 and when i run a calculation like this, in the variables section:

total_sales_volume_tp1=sum({1<[TP]={$(TP1)}>} TOTAL Volume)

the field "Volume" is summed up just fine.

now, when the data field looks like this "2017 Q3", the same calculation is blank.  looking at the raw data, the field "Volume" does have numerical data in it but it seems that any row which has a space in the time period field has no volume to sum up.  i tried this:

=sum({1<[TP]={$("TP1")}>} TOTAL Volume) and that not only didn't solve the problem, it broke all the other rows.  any ideas, please?  thanks

becki kain

[vishsaggi]

Need more information with some sample data!

[becki_kain]

excel and qlik enclosed

[vishsaggi]

Try single quotes like

= sum({1<[TP]={ '$(TP1)'}>} TOTAL Volume)

[Anonymous]

change your TP1 variable to

= '[' & Subfield(GetFieldSelections(TP, ','), ',', 1) & ']'

[becki_kain]

no, that didn't help but thanks.  I tried both single quotes and it didn't help where the spaces were and broke the underscores.  thanks

[becki_kain]

that works but then what do I do for the caption of the box:

=TP1 &' ' &'Retail Share '&TP2

now TP1 is [2016 Q2] not 2016 Q2 and people are fussy about their captions.  how do I split the [] off, for the caption?  thanks

[vishsaggi]

What is your expected output based on the file you enclosed? Can you send us the output you are looking for?

[Anonymous · Accepted solution]

You could either create duplicate variables, one with [] and one without  OR

you'd have to do

=prugechar(TP1 &' ' &'Retail Share '&TP2,'[]')

[becki_kain]

purgechar!  thanks!  i'm still learning the huge amount of commands there are, for qlik