topic Re: set analysis with expressions in QlikView

set analysis with expressions

vdcastro — Wed, 16 Nov 2016 18:06:42 GMT

Hello everyone,

i am trying to solve a problem with no sucess.

i have this dataset

Cod	Control	Problem	Date
COD01	A	ID1	10/10/2016
COD01	A	ID2	10/10/2016
COD01	B	ID1	14/10/2016
COD01	B	ID2	11/10/2016

and i want to count the problems that are not closed after 3 days in point A.

Ex: i have 1 problem alive (14/10/2016 point B) 3 days after 10/10/2016

i want to create a table with this output:

Cod	Problems
COD01	83950
COD02	53882
COD03	77741
COD04	77354

but my current result is:

Cod	Problems
COD01	450
COD02	288
COD03	19761
COD04	77354

my expression is:

=count({< Control= {'B'}, Date={">=$(=(max( {$< Control={'A'}>}date)+3 ))"}>} Problem).

the problem i think i have is that my Date field is with the value of the date of my last row (COD04) because the last row is correct.

I think if i force the Cod on this expression, i obtain the correct result in the table.

Is this possible? something like

=count({< Control= {'B'}, Date={">=$(=(max( {$< COD_BY_DIMENSION, Control={'A'}>}date)+3 ))"}>} Problem).

Thanks by your help

Re: set analysis with expressions

sunny_talwar — Wed, 16 Nov 2016 18:22:43 GMT

From what you have mentioned above, it seems that set analysis might not work for you. May be you need to use Aggr() function. But I am not 100% sure. Would you be able to provide a sample document with the expected output based on the sample provided?

Re: set analysis with expressions

johnw — Wed, 16 Nov 2016 21:03:54 GMT

The quick answer is that set analysis expressions cannot be sensitive to dimensions so you can't do it the way you're trying to do it. There's technically a way around that, but it's hideously complex, and we don't need to go there for this problem.

We need to check the dates for each problem in each specific Cod. That's an aggr() by Cod and Problem.

aggr(...something...,Cod,Problem)

Now, what do we need for the "something"? It would need to return a count of 1 or a single problem ID if for that specific problem, the B point is > 2 days past the A point. So we want to say something like this:

if(Control B Date - Control A Date > 2, 1)

It would be good to use set analysis to identify the control B date and control A date of interest, in the name of performance. But didn't I just say we couldn't do that? Well, sort of, but not exactly. It's the set itself that is only evaluated once, but we can use a set expression where that's just fine. We need an aggregation function to use set analysis, so just in case there are multiple dates, I'll go with max() or min(). So I figure something like this:

if(max({<Control={'B'}>} Date) - min({<Control={'A'}>} Date) > 2, 1)

And I tend to "cheat" with my Boolean algebra in a case like this. I know that an expression that is true evaluates to -1, false to 0. So I can rewrite the above as:

-(max({<Control={'B'}>} Date) - min({<Control={'A'}>} Date) > 2)

I know, I know, it doesn't save more than a couple characters, and is harder to read, but I'm just so used to seeing them that way. Do whichever makes you more comfortable. Anyway, we plug that into our aggr:

aggr(-(max({<Control={'B'}>} Date) - min({<Control={'A'}>} Date) > 2),Cod,Problem)

But we aren't done, because the aggr() will return multiple rows for each Cod. We want the count of those rows with a 1 on them. Easy enough, just sum, and that will give us that count.

sum(aggr(-(max({<Control={'B'}>} Date) - min({<Control={'A'}>} Date) > 2),Cod,Problem))

And I typically put the - on the outside in a case like this, and then I doubt we'll need the inner parentheses around our Boolean expression:

-sum(aggr(max({<Control={'B'}>} Date) - min({<Control={'A'}>} Date) > 2,Cod,Problem))

I might have it wrong, but if I've understood your question and not made a mistake, that should work. That said, a more understandable version might read like this:

count(aggr(if(max({<Control={'B'}>} Date) - min({<Control={'A'}>} Date) > 2,Problem),Cod,Problem))

Same basic idea, but now we're counting problems instead of negative summing a boolean expression, which is probably more clear. Clarity is good, and I have an unfortunate tendency to sacrifice it on the altar of brevity. Mea Culpa.

Re: set analysis with expressions

vdcastro — Thu, 17 Nov 2016 09:24:15 GMT

Thank you by your answer.

I understood your logic and your explanation. Amazing

Thank you very much