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

    getting territory value in text box abject

    Dhanshri Kalgaonkar

      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