topic Re: Dual set-analyses loses dimension in piviot table in QlikView

Dual set-analyses loses dimension in piviot table

Thu, 22 Aug 2013 20:24:16 GMT

Hi all!

I have a piviot table with a dimension "ID" and a set-analyses as expression:

=Count({$<DateTime-={">$(=Min({<Station_ID={44}>}Arrival,2))"}>}DateTime)

The set expression is doing the following:

1. Picking the secound eariest date of Arrival when Station_ID is 44 for the ID in the dimension

2. Excluding all dates for the given ID in DateTime which is after this date

3. Counting how many rows there are left in DateTime

The set-analyses gives out 0 for all ID's.

ID	Set analyses
32-338	0
32-339	0
32-340	0
32-341	0

But when I select a ID it gives out the right answear

ID	Set analyses
32-339	320

It seams that the secound set-analyses >$(=Min({<Station_ID={44}>}Arrival,2)) cant read the ID for the actual row.

Is it any way to call for the value in the ID column? Something like:

=Count({$<DateTime-={">$(=Min({<Station_ID={44}, ID={get value from first column}>}Arrival,2))"}>}DateTime)

Or is it possible to solve it in anouther way? I have tried to use aggr but it gives out the same answear.

=Count({$<DateTime-={">$(=(aggr(Min({<Station_ID={44}>}Arrival,2),ID)))"}>}DateTime)

Re: Dual set-analyses loses dimension in piviot table

swuehl — Thu, 22 Aug 2013 20:54:36 GMT

If you are using a dollar sign expansion with an expression like

$(=Min(Arrival))

then this will be evaluated first, in global context, not considering any dimensions of your chart (it's like a macro expansion, it will be evaluated before anything else get's done).

Also your set expression is only evaluated once per chart, not considering your dimension

(see this document for more details).

So set analysis alone will not work here, and you probably need to use some other technique, like advanced aggregation.

But it would be good if you could post some more details, best by posting a small sample app that demonstrate your issue.

Re: Re: Dual set-analyses loses dimension in piviot table

Tue, 27 Aug 2013 07:09:09 GMT

Thanks for youre reply @swuehl!

You are right that Set-analysis would not work for dual set-analyses, however I have found that it does work for a single set-analyses. When i only use one the expression it will consider my dimension. (see column 2 in Test table in attached file)

Since I cant use dual set analyses for this purpose, could you please elaborate a bit on what you mean by advanced aggregation?

Do you mean aggregating it in the load script?

I have no experience of that, could you give me an example to lead me in the right direction? or eaven better write it in the qv-file?

Thank you for taking time for my issue!