topic Re: Help with Set analysis - Part II in QlikView

Help with Set analysis - Part II

Fri, 19 Aug 2011 17:40:48 GMT

Hi Everyone some days ago i did ask a similar question and got very useful feedback, I hope i can get some help again

The scenario is similar, on a straight table, I have grouped rows on each row, since its dimention is based on Date, (ex. Quarter Hour, Day, etc)

Now the problems is that in an expression, no mater what is the dimention grouping (by Quarter Hour or Day) I must calculate a sum based on the following criteria:

There is a (ContactSateID = 4) in the current bucket
There is a (IsInqueue = 1) in the current bucket

Then is that is met, calculate the sum of (ContacDuration) or the (IsInQueue = 1) between the:

Max(${<ContactStateID = 4>} ContactStartDateQH)

and

Min(${<IsInqueue = 1>} ContactStartDateQH)

The previous filter I created it the following way:

sum(

{$<

ContactStartDateQH = {"= ContactStartDateQH < max({$<ContactStateID={4}>} ContactStartDateQH ) "}

,ContactStartDateQH = {"= ContactStartDateQH >= max({$<IsInQueue = {1}>} ContactStartDateQH ) "}

,IsInQueue = {1}

ContactDurationQH

)

The wrapping it around an Aggr function, but the problem is that if I take each of the filters out, they behave as expected, the Max{$<>} get the max from the current group and so does the Min{$<>}, but when they are combined, it brakes.

I have tried to come up with some sort of

sum(

{$<

ContactStartDateQH = {">= Max{$<....>}ContactStartDateQH < Min({$<...>} ContactStartDateQH ) "}

,IsInQueue = {1}

ContactDurationQH

)

But I got 0 results back.

I am attaching a file that reflects 100% the scenario that I am working on if you want to play with it

Thanks in advance for any suggestions or comments about the best way to create this, dynamically grouped filter

Help with Set analysis - Part II

Mon, 22 Aug 2011 02:41:26 GMT

By the way, I tried to include another Expression with the following code:

concat({$<IsInqueue = {1}>}ContactDurationQH, ',')

To show the records that poteentially could be taken in consideration for the SUM, but the call is returning weirds results, since is not filtering correctly:

What could be the reason for this?

Regards,

-Ed

Re: Help with Set analysis - Part II

lironbaram — Mon, 22 Aug 2011 06:08:39 GMT

hei

i changed a little bit your expressions formulas

have a look

hope its helps you

Re: Help with Set analysis - Part II

Mon, 22 Aug 2011 15:51:53 GMT

Thanks a lot Liron your suggestion definately helps, I just have one more question, inside the logic you have this:

ContactStartDateQH = {"<$(=max({$<ContactStateID={4}>} ContactStartDateQH )) >=$(= min({$<IsInQueue = {1}>} ContactStartDateQH )) "}

which means

ContactStartDateQH = < X >=X

That is just fine, but I was performing a fulltest with this logic and found a case where I needed it to be

ContactStartDateQH = <= X >=X

and then I changed it to be <=, but this broke the logic and now its giving wrong, results, how would you arrange the logic so both <= and >= are considered in the filter?

Thanks in advance for all the time, I really appreciate it.

-Ed

Re: Help with Set analysis - Part II

lironbaram — Tue, 23 Aug 2011 06:25:19 GMT

hei

i will explain what is not working is up to you to decide what to do

when you change the formula to

sum(
aggr(
//Expression
sum(
{$<
ContactStartDateQH = {"<=$(=max({$<ContactStateID={4}>} ContactStartDateQH )) >=$(= min({$<IsInqueue = {1}>} ContactStartDateQH )) "} ,
IsInqueue ={1}
>}
ContactDurationQH
)

//Dimensions
,ContactStartDateQH
,ContactID
)
)
nothing changes because of the second part of the set analysis

the IsInqueue ={1} mean that it including only lines with the value 1 your max line has value 0 so it dosent chnge the resualt

if you remove the IsInqueue ={1} from the expression it will work , but idont know is this what you want

i am attaching an example with the new expressions

Re: Help with Set analysis - Part II

Tue, 23 Aug 2011 14:32:17 GMT

You are right, maybe i did something else while testing it.

Thanks a lot, it works perfectly now.

-Ed