9 Replies Latest reply: Feb 18, 2014 12:27 PM by Robyn Talley

Set Analysis in Dimension

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

• Re: Set Analysis in Dimension

Try this example with your excel.

Let me know if works

Regards

• Re: Set Analysis in Dimension

the attachment file called "example.qvw" is there

• Re: Set Analysis in Dimension

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)))))

• Re: Set Analysis in Dimension

try to review my example

• Re: Re: Set Analysis in Dimension

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!

• Re: Set Analysis in Dimension

see attachment

• Re: Set Analysis in Dimension

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)

• Re: Re: Set Analysis in Dimension

I think in that way (attachment)

• Re: Set Analysis in Dimension

Thank you that works.