4 Replies Latest reply: Aug 8, 2018 1:48 PM by Dhanshri Kalgaonkar

# getting territory value in text box abject

Hi Everyone,

I have two charts in which

chart 1 showing sum of naïve pts across territory and

chart 2 showing sum of new prescriber across territory .

I need to show two text boxes  in which

text box 1 : I need to show territory with maximum naïve pts. ie.  territory name and number of naïve pts of maximum numbers.

text box 2: I need to show new prescriber of maximum naïve pts. ie. territory name of maximum naïve pts and releted new prescriber number.

I am able to get values for first text box.

'Great win ' & chr(32) & FirstSortedValue(distinct(Territory),aggr(-Count({<[Naïve/Return]={'New'}>} distinct([HUB ID])),Territory)) & chr(32) &

' on ' & chr(32) & \$(vMax_Naive_Pts) & chr(32) & 'Naive Patients.'

where:

vMax_Naive_Pts) = max(aggr(Count({<[Naïve/Return]={'New'}>} distinct([HUB ID])),Territory))

for second text box :

='Well done ' & chr(32) & \$(vnew_prescriber_territory)& chr(32) & ' on ' & chr(32) & num#(sum(aggr( NEW_PRESCRIBERS_YTD , only vnew_prescriber_territory)),[HUB ID]) & chr(32) & 'New Prescribers.'

I am using this formula able to get territory name but not able to get new prescriber number related to territory. it's giving me 0 as output.

it will be very helpful if someone able to help me here , need more guidance towards how to get values for particular territory.

Thank you very much for your time!

Best Regards,

Dhanshri

• ###### Re: getting territory value in text box abject

What is the expression behind \$(vnew_prescriber_territory)?

• ###### Re: getting territory value in text box abject

Hi sunny ,

vnew_prescriber_territory = FirstSortedValue(distinct(Territory),aggr(-Count({<[Naïve/Return]={'New'}>} distinct([HUB ID])),Territory))

• ###### Re: getting territory value in text box abject

This is the same exact one like one specified above, isn't it?

It works once, but not the second time? Is that what the issue is?

• ###### Re: getting territory value in text box abject

I use same formula to get territory name with maximum naïve pts. I am having problem with getting new prescriber number related to that territory (territory with maximum naïve pts).

I am trying to get that value with this formula:

num#(sum(aggr( NEW_PRESCRIBERS_YTD , only vnew_prescriber_territory)),[HUB ID]) & chr(32)

it's giving me 0 as an answer.