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

Matching Number of Computer to Software Usage

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.

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

For second part of your question try

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

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

Does this look right to you?

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.