topic How to Concatenate result of aggregation with an string in QlikView

How to Concatenate result of aggregation with an string

khigaurav999 — Wed, 25 Nov 2020 16:16:04 GMT

Hello friends,

I am very new to QlikView... I am learning and exploring it. Certainly this is an awesome tool but I am running into obstacles with this expression that calculates sum of hours and adds hrs to the value from sum/agg. sum([FUNDED_HRS]) & 'hrs'

Tried following as well but didn't get success it says "No data to display" but when I keep only sum([FUNDED_HRS]) it shows accurate number / value on data points.

- sum([FUNDED_HRS]) & ' ' &'hrs'

- concate(sum([FUNDED_HRS]) ,' ' , 'hrs')

Same issue occures on a diff. expression where I am trying to concatenate value / result of 2 diff. aggregate functions.

- Sum([Value]) & '/' & Sum([Funded_Hrs)

Sorry if it is a silly question. Please help.

Thanks in advance!

Gaurav

Re: How to Concatenate result of aggregation with an string

girirajsinh — Mon, 16 Jan 2017 12:53:58 GMT

Hi Gaurav,

sum(field)&'hrs' ...I guess you are tying to put suffix 'hrs' to the value.

That you can do from the Number tab and define format there, as shown below

For Concating to expressions, that too should work fine.

Re: How to Concatenate result of aggregation with an string

khigaurav999 — Mon, 16 Jan 2017 14:00:18 GMT

girirajsinh

This is helpful. I was able to achieve suffixing 'hrs' to the value from Number tab formatting. Thank you!

However concating values of two aggr. functions doesn't work and continues to show "No data to display". This is what i am writing in the expression: Sum([TOV]) &' / '& Sum(FUNDED_HRS)

Only Sum([TOV]) or Sum(FUNDED_HRS) works fines and shows values on data points. I actually want to achieve following format in the output as values on data points... example: $99K / 31K hrs where 99 comes from Sum([TOV]) and 31 comes from sum([FUNDED_HRS]). Not sure if this can be achieved thru expression: 'S' & Sum([TOV]) &'K / '& Sum(FUNDED_HRS) & 'K hrs'

Also, fyi I have 2 dimensions on this chart i don't think no. dimensions matters here but just wanted to let you incase it does. Here is the screenshot of a chart from xls that I am converting in QV:

Thanks,

Gaurav

Re: How to Concatenate result of aggregation with an string

girirajsinh — Mon, 16 Jan 2017 15:52:29 GMT

Hi Gaurav

If your query is resolved please close the thread by marking response as correct

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: How to Concatenate result of aggregation with an string

girirajsinh — Mon, 16 Jan 2017 16:40:17 GMT

Hi Gaurav

You can do that in following way.

I have setup a similar case like yours.

Dim1 : Year

Dim2: Months

Object Bar Chart

Values which you intend to display in some concatenate manner above the Bars, Put it as a separate new expression.

and uncheck Bar representation for it and Check "values on data point ".

I hope you like it.

Re: How to Concatenate result of aggregation with an string

khigaurav999 — Wed, 18 Jan 2017 09:26:28 GMT

Hi girirajsinh,

Thanks for your kind help with this. This helps to achieve concating values of two aggr. functions and showing it on top of data points but it leads to another issue.

It is actually showing values only one segment (largest segment) rather than showing consolidated sum of all the segment.

For example: when i just do simple sum([TOV]), it shows correctly i.e. 128.72 = 84.92 + 43.80

But when i do it with expression '$' & (Sum([TOV])/1000) &'K / '& (sum([FUNDED_HRS])) & 'hrs' like mentioned above in the thread, it shows value for only one segement i.e. 84.92 . deosn't include the value of yellow segment in this case. any idea ?

Thank you,

Gaurav

Re: How to Concatenate result of aggregation with an string

khigaurav999 — Thu, 19 Jan 2017 12:14:54 GMT

Hello friends,

can anyone help with this issue?

Thanks,

Gaurav

Re: How to Concatenate result of aggregation with an string

oknotsen — Thu, 19 Jan 2017 12:44:50 GMT

Your question has been flagged as answered.

If you have a new question, start a new topic, as a lot of people will ignore topics that are flagged as answered.