0 Replies Latest reply on Apr 21, 2017 6:14 AM by Akash mishra

    top 2 city for different set of people

    Akash mishra

      Hi I need a help in creating a complex dashboard where I want to show top 2 concerned city based on a condition for different set of people.

      Let me explain the requirement based on sample Superstone data set.

       

      I have dimensions i.e order date,locality(assuming),city,state and measures as sales and profit respectively.

       

      I need 2 set of dates 1- benchmark months  ( this is fixed date range i.e from april 2014 to october 2014 )

      2- Comparison date ( This has to be dynamic and user will choose any month between November,December)

      I have to create calculated measures as

      1- Benchmark Sales (Sales from april 2014 to october 2014)

      2- Benchmark Profit (Profit from april 2014 to october 2014)

      3- Benchmark profit percentage ( this will be SUM([Benchmark Profit])/ABS(SUM([benchmark sales value])) )

      4- Benchmark state profit percentage ( This will be fixed profit percentage of the selected state irrespective of city {FIXED [State] : [benchmark Profit %]} )

      5- Opportunity ( this is calculated by (([benchmark Profit %]-sum([Benchmark State profit %]))*(sum([benchmark sales value])/7)) and this will be the opportunity loss/profit of city wrt the state)

      6- Comparison sales ( this will be passed by the user and this will be either november 2014 sales or december 2014 sales)

      7- comparison profit ( this will be  passed by the user and this will be either november 2014 profit or december 2014 profit)

      8-comparison profit percentage ( this will be SUM([comparison Profit])/ABS(SUM([comparison sales value])) )

      9- comparison state profit percentage ( This will be fixed profit percentage of the selected state irrespective of city {FIXED [State] : [comparison profit %]}

       

      The localities will be sorted based on the decreasing order of the opportunity.

      Now there will be 3 set of people

      1- state manager - (this person will be responsible for top two concerned localities of the state)

      2- Asst state manager - (This person will be responsible for 3rd and 4th concerned localities of the state)

      3- City Manager -  (This person will be responsible for top two concerned localities of the city)

      now the top two concerned localities for a  city manager will not have any locality listed which has been already listed in state manager and asst state manager level.

      Note :- since superstone data is till city level but assume it is further drilled down to locality code.

       

      I want to represent this into a map where there will be two filters 1st will be comparison date i.e november,december

      and second will be the concern person and this will be having state manager, asst state manager, city manager

      along with this there will be filter for state(single value filter) and city(single value filter).

       

      Now when the user will select a state say Florida and concern person as State manager then the map will show top two localities under florida state

      and when the user will select a state say Florida and concerned person as asst state manager then the map will show the 3rd and 4th locality under Florida state

      but when the user will select a state along with concerned person as city manager then the filter will show the top two locality under the city (excluding the cities that have already been concerned by the state manager and city manager).

       

      The problem I am facing for the concerned person as 'city manager' for which if the user selects any city then that should show the top 2 concerned locality for that city excluding the one that has been already listed by higher managers .

       

      Any Help will be appreciated..

       

       

       

       

      https://public.tableau.com/views/samplesuperstonedatamodified/Dashboard1?:embed=y&:display_count=yes

       

       

       

       

       

       

       

       

      Mahfooj Khan Manideep Bhattacharyya Dinesh Lodhi

       

       

       

      Regards,

      Akash