2 Replies Latest reply on Oct 25, 2018 9:23 PM by Hari Ankem

    Matching Number of Computer to Software Usage

    David Gabra

      Hi

      Please can you help show the number of computer that an employee has vis-a-vis the number of the software licences.

      I have attached a sample file.

      Please explain, how you did it, (if you have time) - i like to learn.

       

       

      I would like the below - on Tableau

      Logic -

      1. Per Employee it must be done

      2. To count the number of Computer per employee (nature column)  - and add result to the 'Results - Computer' column

      3. To count the number of Software or Software Batch (same) per employee (nature column) and add result to the 'Results - Software' column  (THE SOFTWARE MUST HAVE THE SAME NAME TO BE COUNTED AS A DUPLICATE

      4. if there is duplicated software based on the number of computer -  please add Exception - 'Yes' -otherwise 'No Exception.  ie. 1 duplicated to 1 computer is yes -   2 same software for 2 computer is no, of course.

      5. This the complicated and key bit - are you able to calculate the additional cost - ie. the amount of additional licences (difference between the number of computer and duplicates software you calculated ) * the cost of the license.  Please add to the Exception or you can create a new column,

      6 - if there are no computer - then n/a - since

       

      I really hope I was clear.

       

               

      Employee NumberSotware / Comp NameNatureUnit CostResults -# DuplicatesResults - 1 ComputerException Addition Cost Reason to help
      111QQQSoftware license4511Yes 45yes - duplicate software with the same name
      111QQQSoftware license4511Yes45yes - duplicate software with the same name
      111T450Computer4511Yes45yes - 2 software with the same name
      222EEEESoftware license3402No0No - since there are 2 computer and 2 software
      222EEEESoftware license5402No0No - since there are 2 computer and 2 software
      222T600Computer5402No0No - since there are 2 computer and 2 software
      222T600Computer32302No0No - since there are 2 computer and 2 software
      333QQQSoftware license45N/A - No computer1No0No only software and no computer
      333TTTSoftware license45N/A - No computer1No0No only software and no computer
      333TTTSoftware license45N/A - No computer1No0No only software and no computer
      444QQQSoftware license4501No 0No Different software names
      444RRRSoftware license4501No 0No Different software names
      444T450Computer4501No 0No Different software names
      555QQQSoftware license4521Yes952 duplicate for 1 computer
      555QQQSoftware license4521Yes952 duplicate for 1 computer
      555T450Computer4521Yes952 duplicate for 1 computer
      555TTTSoftware license5021Yes952 duplicate for 1 computer
      555TTTSoftware license5021Yes952 duplicate for 1 computer

       

       

      Also please  how to extract only the word  after the 1st /   (i.e to show only aaaaa in the column)

      Example of the text to extract - /aaaaaa/bbbb/cccc/22/

       

      thanks a lot

       

      David

        • 1. Re: Matching Number of Computer to Software Usage
          Deepak Rai

          For second part of your question try

          LeftTrim(Left(Replace( your string, “/“,””),7))

          • 2. Re: Matching Number of Computer to Software Usage
            Hari Ankem

            Does this look right to you?

            1.png

             

            Here are the calculated fields that have been created:

             

            1. Results - Computer: AVG({FIXED [Employee Number]:SUM(IF [Nature]="Computer" THEN 1 ELSE 0 END)})

             

            2. Results - Software: AVG({ FIXED [Employee Number]:SUM(IF CONTAINS([Nature],"Software") THEN 1 ELSE 0 END)})

             

            3. Results - Duplicate:

            IF {FIXED [Employee Number],[Sotware / Comp Name]:SUM(IF CONTAINS([Nature],"Software") THEN 1 ELSE 0 END)}>1

            AND {FIXED [Employee Number]:SUM(IF CONTAINS([Nature],"Computer") THEN 1 ELSE 0 END)}>0

            THEN

                {FIXED [Employee Number],[Sotware / Comp Name]:SUM(IF CONTAINS([Nature],"Software") THEN 1 ELSE 0 END)}

            -

                {FIXED [Employee Number]:SUM(IF CONTAINS([Nature],"Computer") THEN 1 ELSE 0 END)}

            ELSE

                0

            END

             

            4. Results - Additional Cost

            {FIXED [Employee Number],[Sotware / Comp Name]:AVG([Results - Duplicate])}

            *

            {FIXED [Employee Number],[Sotware / Comp Name]:AVG([Unit Cost])}

             

            The workbook is attached. Hope this helps.