topic Re: Set Analysis Intersection help in QlikView

Set Analysis Intersection help

mikecrengland — Thu, 02 Apr 2015 15:53:46 GMT

Hello All -

I have a situation where we have two payroll systems that an employee might be paid from. It's considered an exception if the employee is paid out of both systems during the same week. The payroll team has asked for a trending chart of the number of exceptions per week. The data has a flag (1 or 2) that indicates which system paid.

Basically, what I want to do is to count employees that have both flags in a week. The dimension is WeekStart and the expression is (I've tried with and with out quotes around the numbers):

Count({<Payroll.RSFlag = {1}> * <Payroll.RSFlag = {2}>} Distinct Employee)

Shouldn't this give me a count of employees with both flags, but not one or the other? My chart gives me 0.

Thanks in advance,

mike

Re: Set Analysis Intersection help

pokassov — Thu, 02 Apr 2015 16:32:45 GMT

Hi, Mike!

Could you try expression below?

Sergey.

count(aggr(if(min(Payroll.RSFlag)<>max(Payroll.RSFlag),Employee),Call.WeekStartMon))

Re: Set Analysis Intersection help

MK_QSL — Thu, 02 Apr 2015 16:33:07 GMT

SUM(Aggr(COUNT({<Payroll.RSFlag = {1}, Employee = P({<Payroll.RSFlag= {2}>}Employee)>}DISTINCT Employee),Employee))

Re: Set Analysis Intersection help

pokassov — Thu, 02 Apr 2015 16:44:20 GMT

Hello, Manish!

I have checked your expression with my test example.

It looks like there is a mistake...

Could you check?

t1:

LOAD * Inline [

Employee, Call.WeekStartMon, Payroll.RSFlag

ADALEE, 2015-03-09, 1

ADALEE, 2015-03-09, 2

ADALEE, 2015-03-16, 1

ADALEE, 2015-03-23, 1

BBBAAA, 2015-03-23, 2

BBBAAA, 2015-03-30, 1

BBBAAA, 2015-03-30, 2

];

2015-03-23 - your expression shows 1, meanwhile the right answer is 0 because there are different employees in this week. not the same.

Sergey

Re: Set Analysis Intersection help

MK_QSL — Thu, 02 Apr 2015 16:49:33 GMT

I haven't tested.. but if it is not working, you can try below option...

SUM(IF(Aggr(Count(DISTINCT Payroll.RSFlag),Employee,Call.WeekStartMon)>1,1))

Re: Set Analysis Intersection help

mikecrengland — Thu, 02 Apr 2015 16:49:34 GMT

Hi Sergey,

Unfortunately, this returned 0, but I'm going to keep trying to tweak it.

Thanks!

Re: Set Analysis Intersection help

mikecrengland — Thu, 02 Apr 2015 16:50:23 GMT

Thanks, Manish! This looks like it works.

mike

Re: Set Analysis Intersection help

mikecrengland — Thu, 02 Apr 2015 17:17:26 GMT

Hi Manish -

So, this works for one week, but if I take off the date filter, the line chart explodes and shows 20x the correct number. An example is the week of 2/2 gives me 42, but if I unfilter the date, that same week shows 465.

I vaguely remember something about indirect set analysis and that the p/e part is calculated at the chart level, not the row level so the calculation is based on any that are both 1 and 2, not just during the week dimension. Do you know if that's true?

mike

Re: Set Analysis Intersection help

pokassov — Thu, 02 Apr 2015 17:23:47 GMT

Mike, can you attach your model with some example data?

Re: Set Analysis Intersection help

mikecrengland — Thu, 02 Apr 2015 17:45:06 GMT

Hi Sergey -

I actually got it to work by cheating a little. Instead of a line chart, I used a bar chart with expressions for 8 weeks. (We have a field for relative weeks built into the master calendar so it will roll) Then uses Manish's formula.

Thank you for your help!

mike