Set Analysis in Dimension

[#1]

Hi There!

I need assistance in calculating a dimension with set anaylsis in my straight table. 

I need my straight table to provide a count of employees who have 1 sale, 2 sales, 3 sales, 4 sales, 5, sales, etc. each week. 

I would like my straight table to look something similar to the below.  I have my data in an excel spreadsheet.  I only want to count SaleId's that have boolean value of 'Yes' in the [Use] field.

Any help would greatly be appreciated.  I have never created a calculation in the dimension that uses set anaylsis.  THANKS!

Count of Sales               Count of Employees

          1                                   20

          2                                   48

          3                                   54

          4                                   60

          5                                   70

          6                                   45

          7+                                 27

[#2]

Try this example with your excel.

Let me know if works

Regards

[#3]

please see my post.

the attachment file called "example.qvw" is there

[maxgro]

see attachment

[#5]

I was thinking of an expression in my dimension similar to something like the below calculated dimension where I am grouping my dimension by months of hire after the employee's first sale.

I just dont know how to write the calculation using set anaylsis to only group the sales with a 'Yes' in the [Use] field.  Thanks!

=if([NumberOfDaysSaleIDSubmittedAfterEmployeeHireDate] <= '60' , dual('0-2',1),

           if([NumberOfDaysSaleIDSubmittedAfterEmployeeHireDate] > '59'

     and [NumberOfDaysSaleIDSubmittedAfterEmployeeHireDate] < '121', dual('3-4',2),

           if([NumberOfDaysSaleIDSubmittedAfterEmployeeHireDate] > '120'

     and [NumberOfDaysSaleIDSubmittedAfterEmployeeHireDate] < '181', dual('5-6',3),

           if([NumberOfDaysSaleIDSubmittedAfterEmployeeHireDate] > '180' , dual('7+',4)))))

[#6]

Hi Massimo -  Thanks for the reply.  How could I use set analysis in the expression you provided where I am only counting those sales with a 'Yes'?  Thanks!

aggr(count(SaleID), [Employee Name], Week)

[#7]

Please can  you attach your example?

try to review my example

[#8]

Hi Luis -  I opened your example.  Thank you.  I have to still manually filter on the USE field to only look at sales with a 'yes' value.  I was thinking I need something similar to the attached example but I need the calculation in the dimension field to exclude the 'no' values from the [USE] field.  Thanks!

[maxgro · Accepted solution]

I think in that way (attachment)

[#10]

Thank you that works.