topic Re: Text Box Formatting in QlikView

Text Box Formatting

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

I have a text box with the expression

='Onboarding Time' & chr(10) &

NUM(Sum(

Aggr(

MAX({$<JobCode={'PST'}>} PersonJobEndDate)-ScribeUStartDate

, PersonGUID)

)

Count({$<JobCode={'PST'}>} DISTINCT PersonGUID),'##')

However for the values I get values in the attached image. Do anyone know how to fix this issue to show right?

Re: Text Box Formatting

sunny_talwar — Mon, 11 Jun 2018 14:31:46 GMT

How about this (added a decimal to the format ##.)

='Onboarding Time' & chr(10) &

NUM(Sum(

Aggr(

MAX({$<JobCode={'PST'}>} PersonJobEndDate)-ScribeUStartDate

, PersonGUID)

)

Count({$<JobCode={'PST'}>} DISTINCT PersonGUID),'##.')

Re: Text Box Formatting

taylor_jesse — Mon, 11 Jun 2018 14:37:46 GMT

Quick question Sunny,

I have the same expression in a straight table however the straight table displays the default value when nothing is selected but when the user selects a location, the text box displays. Although they have the same expression, they give different values for the same location.

Do you know how I can double shoot this?

Thank you

Re: Text Box Formatting

sunny_talwar — Mon, 11 Jun 2018 14:39:52 GMT

When a location is selected, straight table shows no value, but the text box does?

Re: Text Box Formatting

taylor_jesse — Mon, 11 Jun 2018 14:45:09 GMT

The straight table pretty much shows the default of every location and its values but when a location is selected the straight table disappears and a text box shows with the breakdown.

For instance when nothing is selected the straight table may display 15 as onboarding time for California but when California is selected by the user, the text box may show 19 as onboarding time. Same expression in box places just displays different values sometimes.

Re: Text Box Formatting

sunny_talwar — Mon, 11 Jun 2018 14:47:13 GMT

I am not sure, but may be you need location in your aggr function

='Onboarding Time' & chr(10) &

NUM(Sum(

Aggr(

MAX({$<JobCode={'PST'}>} PersonJobEndDate)-ScribeUStartDate

, PersonGUID, location)

)

Count({$<JobCode={'PST'}>} DISTINCT PersonGUID),'##.')

Re: Text Box Formatting

taylor_jesse — Mon, 11 Jun 2018 14:52:35 GMT

It actually worked. values are displaying the same in both text box and straight table. Can you please explain why the location in the expression?

Thank you

Re: Text Box Formatting

sunny_talwar — Mon, 11 Jun 2018 14:55:20 GMT

You were only aggregating on PersonGUID but it seems that a PERSONGUID can be from more then a single location. To match the value with the text box... which only shows up when a location is selected... you needed to added location in the straight table function to aggregate not just on PersonGUID, but also location.

Re: Text Box Formatting

taylor_jesse — Mon, 11 Jun 2018 14:58:53 GMT

Makes sense. Thanks a lot Sunny.

This was very helpful.

Thank you