getting territory value in text box abject

[dhanshri_kalgao]

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

[sunny_talwar]

What is the expression behind $(vnew_prescriber_territory)?

[dhanshri_kalgao]

Hi sunny ,

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

[sunny_talwar]

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?

[dhanshri_kalgao]

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.