6 Replies Latest reply on Jul 29, 2016 10:19 AM by Aman Cheema

    Hi Everyone

    Aman Cheema

      I work in retail environment but I'm looking at POS data and looking at anything that may be an indicator that employee might need additional training or they might be up to no good on the till.

       

       

      I have created some fake data below and if anyone can help that would be great. I'm using version 9.0 desktop

       

      I'm looking to get a total for each of the categories (Cash,Debit, Visa,MA, Amex,) 

       

      I want tableu to show who the top ten staff are that use visa type ( I want this broken up in Value and transaction) For example the first two line is the same customer as the trans number is the same. 

       

      So staff 100594 should have one transactions for 111.96.   staff 100594 would be ranked based on how many individual transaction they had and total value of these transactions.

       

      Any help would be great.

       

       

       

       

       

      DateTransStaffDebitCashVisaMAAmexCHQGC
      6/22/2016 14:06787210059465.82
      6/22/2016 14:06787210059446.14
      6/19/2016 14:599250280461-1877.52
      6/20/2016 15:515821280461-863.88
      6/25/2016 17:00203369955313.81
      6/27/2016 16:5194262804613.08
      6/28/2016 14:20414222640-12.74
      6/28/2016 14:204142903393-9.21
      6/28/2016 14:231925463836-25.91
      6/28/2016 14:321932904144-90.24
      6/28/2016 16:024252925222-26.21
      6/28/2016 16:244268648006-23.09
      6/28/2016 16:244268834390-17.34
      6/20/2016 16:581633597070-16.19
      6/22/2016 12:29191819968-12.16
      6/24/2016 17:188021374686-20.79
      6/27/2016 12:012894583427-12.46
      6/27/2016 12:012894583427-12.46
      6/28/2016 14:003228923318-26.21
      6/28/2016 15:163286224501-2.62
      6/19/2016 15:133701239525-12.43
      6/20/2016 18:0540771610-25.39
      6/21/2016 14:119986890996-39.29
      6/21/2016 16:00951651193-24.81
      6/21/2016 16:17134255380-2.62
      6/22/2016 10:11377132902-8.98
      6/22/2016 10:52409175976-16.8909
      6/22/2016 13:109548235499-15.14
      6/22/2016 14:249560905000-8.98
      6/22/2016 15:144608341404-14.46
      6/22/2016 16:524704150144-20.79
      6/23/2016 17:265231131094-26.21
      6/26/2016 11:16137505586-5.84
      6/26/2016 16:04362361527-17.63
      6/26/2016 16:04362464388-10.44
      6/27/2016 15:28704904540-10.94
      6/19/2016 15:525749693093-14.39
      6/20/2016 16:395966268714-18.49
      6/20/2016 17:496008687491-14.01
      6/22/2016 14:06635865185-29.41
      6/24/2016 9:346814512384-12.55
      6/25/2016 13:517388906354-39.19
      6/25/2016 13:527390552554-25.91
      6/25/2016 15:427489509307-24.19
      6/26/2016 14:147760186510-15.88
      6/26/2016 16:487856547661-49.54
      6/27/2016 14:417995824920-13.24
      6/27/2016 17:548140771535-13.58
      6/19/2016 17:35494647464-13.81
      6/21/2016 16:355484309575-16.76
      6/23/2016 13:576017911404-22.23
      6/23/2016 16:416123418889-6.3
      6/26/2016 15:45751429306-13.58
      6/27/2016 15:567796585083-15.88
      6/28/2016 18:078284197186-13.08
      6/20/2016 14:155795383711-20.88
      6/23/2016 17:57642018-15.88
      6/23/2016 17:5764204390-37.56
      6/24/2016 15:536534592865-16.19
      6/25/2016 17:446905404913-17.34
      6/27/2016 14:0469811487-28.26
      6/27/2016 14:0469811487-28.26
      6/19/2016 14:285477614354-31.71
      6/19/2016 14:44373763149-8.98
      6/19/2016 14:443737622282-13.61
      6/19/2016 15:293792823229-14.23
      6/20/2016 10:475747284893-16.29
      6/20/2016 16:06398964089-3.54
      6/21/2016 13:384274558908-30.66
      6/21/2016 17:136499923383-14.39
      6/22/2016 15:214945432856-2.5
      6/22/2016 18:015150316570-19.06
      6/22/2016 18:015150316570-19.06
      6/22/2016 18:015150388629-23.66
      6/22/2016 18:015150518530-16.53
      6/22/2016 18:015150786731-18.49
      6/22/2016 19:05522115271-13.18
      6/22/2016 19:055221793430-12.43
      6/22/2016 20:045276906354-39.19
      6/22/2016 20:235291669333-23.61
      6/23/2016 16:28349488254-26.83
      6/23/2016 17:057342678813-11.36
      6/23/2016 17:59481110056-27.11
      6/24/2016 12:176804508754-2.73
      6/24/2016 13:25937186510-15.88
      6/24/2016 19:041311957795-5.84
      6/25/2016 14:106847443028-13.99
      6/25/2016 15:551848785469-14.46
      6/25/2016 17:298365246967-9.86
      6/26/2016 12:488647646067-13.58
      6/26/2016 13:237349685594-2.96
      6/26/2016 13:237349685594-2.96
      6/26/2016 15:21748064089-3.54
      6/27/2016 11:189037736637-2.39
      6/27/2016 18:279351904144-11.28
      6/27/2016 20:487936955393-1.11
      6/28/2016 11:529597180448-9.21
      6/28/2016 12:459613315457-18.18
      6/19/2016 13:036720129031-38.71
      6/19/2016 13:036720129031-38.71
      6/19/2016 13:036720172163-6.53
      6/19/2016 13:036720606426-8.37
      6/19/2016 13:036720989319-41.49
      6/20/2016 12:161800351395-459.36
      6/22/2016 15:372398370833-9.52
      6/22/2016 15:372398370833-9.52
      6/22/2016 15:372398694893-10.13
      6/22/2016 15:372398867341-13.24
      6/22/2016 16:077599251371-7.22
      6/22/2016 17:417674600015-13.24
      6/23/2016 14:222645781419-57.01
      6/23/2016 14:417881135178-10.44
      6/23/2016 15:42794070375-25.96
      6/23/2016 16:167951206201-9.29
      6/23/2016 16:167951772574-14.46
      6/23/2016 17:298008310763-12.36
      6/24/2016 13:479548399410-10.44
      6/24/2016 13:479548877654-6.99
      6/24/2016 15:139622775221-6.99
      6/24/2016 15:409652824920-13.24
      6/24/2016 15:539668186510-15.88
      6/24/2016 16:523191399428-18.01
      6/24/2016 20:0833329985-2.79
      6/25/2016 10:368474320135-18.49
      6/25/2016 10:368474366930-33.44
      6/25/2016 13:183480280719-16.29
      6/25/2016 13:183480624882-39.29
      6/25/2016 13:563513336974-13.08
      6/25/2016 17:278690940692-3.35
      6/26/2016 12:473812224501-2.62
      6/19/2016 12:14308728886-23.49
      6/22/2016 10:084045787838-11.59
      6/22/2016 12:2476881453-39.29
      6/22/2016 14:45428471142-15.04
      6/23/2016 14:284338393991-13.81
      6/23/2016 16:587919492298-23.19
      6/23/2016 18:06452271126-15.88
      6/24/2016 13:544932337402-23.7113
      6/24/2016 16:2552689209-15.88
      6/25/2016 16:165957680876-23.34
      6/27/2016 12:346027307587-13.01
      6/27/2016 13:136499585083-63.52
      6/28/2016 15:476676681858-26.83
      6/28/2016 16:426731142992-19.06
      6/20/2016 16:112690591230-2.73
      6/20/2016 18:0199046219-5.84
      6/20/2016 18:01990528505-6.53
      6/20/2016 18:01990879759-13.58
      6/25/2016 12:434167275552-29.99
      6/25/2016 12:54211421998-29.41
      6/25/2016 13:084176168054-13.31
      6/25/2016 16:452323262626-15.88
      6/25/2016 16:452323673566-12.09
      6/25/2016 16:452323734475-20.79
      6/25/2016 16:452323908665-11.28
      6/25/2016 16:452323908665-11.28
      6/25/2016 17:474395525659-13.58
      6/25/2016 17:5644035793-5.72
      6/25/2016 17:5644035793-5.72
      6/25/2016 17:564403606426-8.37
      6/25/2016 19:232447485003-9.29
      6/25/2016 19:232447485003-9.29
      6/25/2016 19:232447485003-9.29
      6/26/2016 12:482547854240-48.57
      6/26/2016 12:482547854240-16.19
      6/26/2016 14:062611188821-4.11
      6/26/2016 15:012642451153-17.91
      6/27/2016 13:482850535294-60
      6/27/2016 15:482890628909-9.29
      6/19/2016 13:375756862375-20.68
      6/19/2016 14:236948164038-13.81
      6/19/2016 16:329302215442-16.29
      6/19/2016 17:596073450866-0.3
      6/20/2016 16:279490614875-3.35
      6/21/2016 14:106714940692-3.35
      6/21/2016 14:106714948869-3.35
      6/21/2016 14:106714948869-3.35
      6/22/2016 17:537281276188-7.17
      6/22/2016 18:528031569418-12.23
      6/23/2016 18:198304886747-6.07
      6/23/2016 18:474863945543-26.21
      6/23/2016 19:028357419721-20.79
      6/24/2016 16:148592785469-14.46
      6/24/2016 16:35108089888-12.63
      6/25/2016 14:335395856302-43.79
      6/25/2016 15:368355540609-24.76
      6/25/2016 18:151529617696-36.99
      6/25/2016 18:198521585547-78.39
      6/27/2016 19:082723414292-17.34
      6/28/2016 10:322884450866-0.06
      6/28/2016 11:052902518530-16.53
      6/28/2016 12:022953833202-11.51
      6/28/2016 14:309702182238-41.49
      6/28/2016 20:195801945543-26.21
      6/22/2016 11:459800450866-0.06
      6/23/2016 13:131325369058-13.31
      6/24/2016 20:411843254946-2.39
      6/24/2016 20:411843768440-6.64
      6/25/2016 14:071925623264-16.76
      6/25/2016 17:542082752410-23.34
      6/25/2016 18:262114439950-13.58
      6/26/2016 14:492311460378-31.14
      6/27/2016 12:452437525253-188.88
      6/27/2016 17:162501450866-0.06
      6/28/2016 14:191111736090-2.79
      6/28/2016 16:011145793810-13.38
      6/19/2016 12:364249272724-25.39
      6/19/2016 12:424257201343-39.86
      6/19/2016 12:424257201343-39.86
      6/19/2016 13:55433812757-10.13
      6/19/2016 15:084412904144-11.28
      6/20/2016 18:065086313908-24.81
      6/20/2016 18:065086321182-21.36
      6/20/2016 18:295110432856-2.5
      6/20/2016 18:295110432856-2.5
      6/20/2016 18:295110432856-2.5
      6/20/2016 18:595144127571-9.52
      6/20/2016 18:595144127571-9.52
      6/20/2016 18:595144200303-13.01
      6/20/2016 18:595144200303-13.01
      6/20/2016 18:595144365601-2.39
      6/20/2016 18:595144365601-2.39
      6/20/2016 18:595144596817-25.34
      6/20/2016 18:595144596817-25.34
      6/20/2016 18:595144698043-8.14
      6/20/2016 18:595144698043-8.14
      6/20/2016 18:595144858126-3.08
      6/21/2016 16:395479875708-13.01
      6/22/2016 15:3658802089-24.81
      6/23/2016 16:256451418921-24.93
      6/24/2016 9:556836557108-32.86
      6/24/2016 16:465421560995-10.13
      6/24/2016 17:0027487264-64.68
      6/24/2016 19:205558110700-14.39
      6/24/2016 20:58749759451-12.74
      6/25/2016 12:277633131938-14.56
      6/25/2016 12:277633459677-10.44
      6/25/2016 12:277633734475-20.79
      6/25/2016 13:147697554089-18.18
      6/27/2016 18:139027487264-3.08
      6/28/2016 12:359200505586-5.84
      6/28/2016 12:359200505586-5.84
      6/28/2016 12:359200842559-9.29
      6/21/2016 11:486767100925-14.23
      6/21/2016 11:486767755835-11.59
      6/21/2016 12:546806324095-17.34
      6/24/2016 12:348460697672-26.83
      6/24/2016 17:308670319327-4.11
      6/25/2016 15:369489635193-15.88
      6/25/2016 15:369489635193-15.88
      6/25/2016 15:369489635193-15.88
      6/25/2016 15:369489635193-15.88
      6/26/2016 12:309927255380-2.62
      6/27/2016 20:37370503821-17.91
      6/28/2016 13:52158571489-17.34
      6/28/2016 17:46348777649-13.01
      6/21/2016 15:25966719968-12.16
      6/21/2016 15:259667428284-15.61
      6/22/2016 14:439798300368-19.06
      6/24/2016 17:02253535294-2.5
      6/24/2016 17:02253535294-2.5
      6/28/2016 17:28867525253-23.61
      6/24/2016 9:366652529123-455.94
      6/22/2016 18:066469882266-248.67
      6/19/2016 17:413662904334-248.16
      6/25/2016 12:127127448548-240.12
      6/22/2016 18:066469301465-201.34
      6/22/2016 18:066469301465-201.34
      6/24/2016 9:366652546564-200.64
      6/22/2016 18:066469558239-140.48
      6/19/2016 16:143592569137-121.99
      6/22/2016 18:066469277988-95.54
      6/22/2016 18:066469882266-82.89
      6/22/2016 18:066469882266-82.89
      6/22/2016 18:066469882266-82.89
      6/22/2016 18:066469882266-82.89
      6/24/2016 9:366652529123-75.99
      6/24/2016 9:366652529123-75.99
      6/22/2016 18:066469890582-74.84
      6/22/2016 18:066469890582-74.84
      6/22/2016 18:066469558239-70.24
      6/26/2016 18:17694253314-40.56
      6/20/2016 10:563802774273-39.19
      6/21/2016 10:366051331-37.56
      6/20/2016 15:19579841384-34.01
      6/20/2016 15:19579841384-34.01
      6/22/2016 17:524886456095-34.01
      6/28/2016 16:117708774315-31.71
      6/22/2016 17:524886368191-30.56
      6/20/2016 15:195798613943-29.71
      6/22/2016 18:289626307587-26.02
      6/23/2016 15:445347537209-25.96
      6/25/2016 22:506563694406-25.96
      6/23/2016 13:015211904334-22.56
      6/24/2016 21:25292175612-22.53
      6/21/2016 15:25427398541-20.79
        • 1. Re: Hi Everyone
          Jeremy Poole

          Thanks Aman Cheema welcome to the retail community. I apologize it has taken me a few days to respond, I was on vacation last week. I'll throw a quick dashboard together this evening if I get a chance and see if it looks like what you're after.

          • 2. Re: Hi Everyone
            Aman Cheema

            Thanks @jeremypoole

            • 3. Re: Hi Everyone
              Jeremy Poole

              Aman Cheema there are a few approaches here that you can take. The one I'll mention first would be that this data set would be simpler to work with if it were 'tall'. Meaning instead of one measure for each tender type, it would be better if tender type were a dimension and there was only one "value measure". You could also pivot this data within Tableau's data pane to achieve the above structure. Either way, your desired result can still be achieved.

               

              Using the structure you provided above, I created one calc and one parameter.

               

              Trans Value $ (calc):

              CASE [Charge Type]

              when "Amex" then [Amex]

              when "Cash" then [Cash]

              when "CHQ" then [CHQ]

              when "Debit" then [Debit]

              when "GC" then [GC]

              when "MA" then [MA]

              when "Visa" then [Visa]

              END

               

              Charge Type (parameter):

               

               

              Using these 2 objects (and filtering on Trans Value $ <> Null) I created the following view controlled by the parameter:

               

              Hope this helps! Let me know if I missed anything you were hoping to see.

              • 4. Re: Hi Everyone
                Aman Cheema

                Thanks I will give this a try. 

                • 5. Re: Hi Everyone
                  Aman Cheema

                  Jeremy

                   

                  Thanks for the above.  I did take your first suggestion and format my data via pivot in tableau.   I came out with couple of other issues and I'm wondering if you can help me with the calculated field to get the right calculation.

                   

                  see the below data breaks staff with how many charge type of transaction they have but the total number is off because it keeps adding the same value as one entry instead two.  For example if two charge type value has the same value  for the same staff it counts it as one instead of two.  As i explained earlier I also want multiple lines which are tied to transactions to count as one instead of the amount of lines on same transcation. 

                   

                  Thanks 

                   

                   

                  • 6. Re: Hi Everyone
                    Aman Cheema

                    Jeremy Poole I got this figured out and working good now.  I thought I save you some time.

                     

                    Thanks appreciate the help.