topic Re: SetAnalysis issue - how to compare two dimensions in QlikView

SetAnalysis issue - how to compare two dimensions

Wed, 25 Nov 2020 16:16:04 GMT

Dear Community,

I'm new person here still learning new thing, and I'm struggling with SetAnalysis function.

I have set of data - fact table storing how much time each request spend in department

And dimension, saying to which department this request is currently signed.

What i need to find is number of request which are in current department for defined period of time ( below 4 hours, 4-8 hours, above 8 hours).

So i would like to get following results:

Dept	Below 4h	4-8	Above 8
A	1
B	1	1
C			1

I tried to write sum function, calculating time spent only for current department, but such syntax doesn't return me correct values:

Sum({$<DayID-=P({1<DepartmentID={$(=CurrentDepartmentID)}>}DayID)>}[Time Spent in h])

I'm attaching file with simple data.

I'll be very gratefull for your help.

Regards,
Greg

Re: SetAnalysis issue - how to compare two dimensions

giakoum — Tue, 29 Nov 2016 11:01:02 GMT

DepartmentID=CurrentDepartmentID should do although it will appear red as if the expression is wrong

Re: SetAnalysis issue - how to compare two dimensions

Tue, 29 Nov 2016 12:07:29 GMT

I marked it red - just to point, that this is part cause problems here.
And it's not working, it returns normal sum.

Re: SetAnalysis issue - how to compare two dimensions

Anonymous — Tue, 29 Nov 2016 12:18:31 GMT

Hi,

Please find attached QVW for your reference and use following dimensions.

Dimension 1:- =if(DepartmentID=CurrentDepartmentID,CurrentDepartmentID)

Dimension 2:- =if([Time Spent in h]<4,'Below 4th',if([Time Spent in h]<8,'4-8','Above 8'))

Expression:- Count(DISTINCT DayID)

Thanks,

Re: SetAnalysis issue - how to compare two dimensions

Tue, 29 Nov 2016 13:19:32 GMT

Hi,

Unfortunately it doesn't show correct values.

It check time spend for each day separately per DayID, and i need to check total time spent in last Department.

eg. RequestID=3 been 2 days in department C, together it is 6+6=12 hours and should be counted as 'Above 8 hours'

Re: SetAnalysis issue - how to compare two dimensions

Anonymous — Wed, 30 Nov 2016 04:37:09 GMT

Hi Mroz,

For following result, please find attached QVW and dimension and expressions.

Dept	Time1	Above 8	4-8	Below 4th
A		-	-	1
B		1	1	-
C		1	-	-

Dimension 1:- =if(DepartmentID= CurrentDepartmentID, CurrentDepartmentID)

Dimension 2:- =aggr( if(sum([Time Spent in h])<4,'Below 4th',if(sum([Time Spent in h])<8,'4-8',

if(sum([Time Spent in h])>8,'Above 8'))),RequestID)

Expression:- Count(DISTINCT CurrentDepartmentID)

Thanks & Regards,

Pooja Singh

Re: SetAnalysis issue - how to compare two dimensions

Anonymous — Wed, 30 Nov 2016 04:38:41 GMT

Hi Mroz,

For following result, please find attached QVW and dimension and expressions.

Dept	Time1	Above 8	4-8	Below 4th
A		-	-	1
B		1	1	-
C		1	-	-

Dimension 1:- =if(DepartmentID= CurrentDepartmentID, CurrentDepartmentID)

Dimension 2:- =aggr( if(sum([Time Spent in h])<4,'Below 4th',if(sum([Time Spent in h])<8,'4-8',

if(sum([Time Spent in h])>8,'Above 8'))),RequestID)

Expression:- Count(DISTINCT CurrentDepartmentID)

Thanks & Regards,

Pooja Singh

Re: SetAnalysis issue - how to compare two dimensions

sunny_talwar — Thu, 01 Dec 2016 02:28:46 GMT

May be you need this:

Dimension

=If(DepartmentID = CurrentDepartmentID, CurrentDepartmentID)

=Aggr(

If(Sum([Time Spent in h]) < 4, Dual('Below 4th', 1),

If(Sum([Time Spent in h]) < 8, Dual('4-8', 2),

If(Sum([Time Spent in h]) > 8, Dual('Above 8', 3)))), RequestID, DepartmentID)

Expression

Count(DISTINCT CurrentDepartmentID)

Re: SetAnalysis issue - how to compare two dimensions

Tue, 06 Dec 2016 08:53:42 GMT

Hi Sunny,

It's almost perfect.

But it don't count request.

When i add another request do Department C (with id=5) - i still see 1 request below 4th in dept. C (insted of two), see example:

Re: SetAnalysis issue - how to compare two dimensions

sunny_talwar — Tue, 06 Dec 2016 10:11:06 GMT

In that case, may be you want to do a distinct count of RequestID

Count(DISTINCT RequestID)

Re: SetAnalysis issue - how to compare two dimensions

Mon, 12 Dec 2016 09:18:39 GMT

This is what I need.

Sunny and Pooja, thank you very much for your help !