How to make set analysis on row level?

[#1]

Hi,

I have a problem:
I would like to count all distinct orders that have been started during the selection. Other orders may be end in the selection.
In Qlikview, the orders are divided in many rows since we need to be able to divide an order on time period down to one hour, while an order often runs much longer than that.
Therefore we have created id:s for each hour.

We have tried with this set analysis but it doesn't give any records, i.e. count=0.

Count ({$<PPTimeMatchId={PPOrderStartTimeId}>} DISTINCT Order_Id)

PPTimeMatchId PPOrderStartTimeId OrderStartTime  Team Order_Id
3000005847 3000005822  2011-08-31 2:36 PM Team 2 1291
3000005847 3000005822  2011-08-31 2:36 PM Team 1 1291
3000005848 3000005822  2011-08-31 2:36 PM Team 1 1291
3000005849 3000005822  2011-08-31 2:36 PM --- 1291
3000005849 3000005822  2011-08-31 2:36 PM Team 1 1291
3000005849 3000005849  2011-09-01 5:06 PM --- 1290
3000005849 3000005849  2011-09-01 5:06 PM Team 1 1290
3000005849 3000005849  2011-09-01 5:06 PM Team 1 1290
3000005850 3000005849  2011-09-01 5:06 PM Team 1 1290
3000005851 3000005849  2011-09-01 5:06 PM --- 1290
3000005851 3000005849  2011-09-01 5:06 PM Team 1 1290
3000005852 3000005849  2011-09-01 5:06 PM --- 1290
3000005852 3000005849  2011-09-01 5:06 PM Team 1 1290
3000005853 3000005849  2011-09-01 5:06 PM Team 1 1290

The above is an example of rows. I would have liked to get count=1 in this example.

Any suggestions how to compare on row level?

Very greatful for answers.

Best regards,

Claes

[swuehl]

I am unclear in which context you want to use above expression. I assume you are aware that set analysis is evaluated once for a chart and that it will not regard the current dimension value.

So I further assume you just put this expression e.g. in a text object. Then you select on PPOrderStartTimeId.

If you want to copy the selection in one field to another field in a set expression, just remove the curly brackets, which are used to create an element list. To pass the selection in one field, just use the field name itself:

Count ({$<PPTimeMatchId= PPOrderStartTimeId >} DISTINCT Order_Id)

This gives a count of 1 at my place (for 3000005849 selected) but I think I haven't got your requirements fully.

Regards,

Stefan

[#3]

Thank you for you answer!

I would like it in a chart with Team as dimension. So that is not possible with set analysis?

The user will select on order, timeperiod, team aso and will not see PPTimeMatchId or PPOrderStartTimeId. That is only for trying to count how many orders was started for each Team.

I have copied it to a text box now and it still shows 0.

Do you have any ideas why and how to solve my problem?

Regards,

Claes

[swuehl]

No idea, why it still shows zero at your place. Again, is your setting maybe more complex than shown? What is timeperiod and how is it linked to your above table?

If I just create a table with dimension team and as expression count(distinct order_id), I get

Team	count(distinct  Order_Id)
	2
Team 2	1
Team 1	2
---	2

Does this looks reasonably to you?

[#5]

There are many linked tables. But when showing it in a table box, these are all records that are shown within the selection. When removing the set analysis, I get the same result as you.

We use Ver 9 SR7. Can that have anything to do with it?

I.e. a bug in that version?

Regards,

Claes

[swuehl]

Claes,

not sure if this is a bug. I assume your issue has something to do with your data model.

Could you post a small sample QV file here to the forum (maybe with scrambled or mockup data if needed, but the data model should be close enough to your real setting)?

And maybe also add another table (excel?) with your expected result, please.

[#7]

I cannot add a qv file unfortunately, but I could share my computer with you through skype or ntrsupport if you have time. My skype address is claes.gunnarsson and then I can post the result/what solved the problem on the community.