topic Re: Set Analysis in Dimension in QlikView

Set Analysis in Dimension

Tue, 18 Feb 2014 16:25:40 GMT

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

Tue, 18 Feb 2014 16:36:54 GMT

Try this example with your excel.

Let me know if works

Regards

Re: Set Analysis in Dimension

Tue, 18 Feb 2014 16:43:22 GMT

please see my post.

the attachment file called "example.qvw" is there

Re: Set Analysis in Dimension

maxgro — Tue, 18 Feb 2014 16:50:42 GMT

see attachment

Re: Set Analysis in Dimension

Tue, 18 Feb 2014 16:55:56 GMT

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

Tue, 18 Feb 2014 16:58:21 GMT

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: Set Analysis in Dimension

Tue, 18 Feb 2014 17:00:45 GMT

Please can you attach your example?

try to review my example

Re: Re: Set Analysis in Dimension

Tue, 18 Feb 2014 17:14:36 GMT

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: Re: Set Analysis in Dimension

maxgro — Tue, 18 Feb 2014 17:21:53 GMT

I think in that way (attachment)

Re: Set Analysis in Dimension

Tue, 18 Feb 2014 17:27:38 GMT

Thank you that works.