Count the number of parent records that have the same number of child records

[#1]

Hi,

I have two tables in my datamodel - one holds booking level information, the other the item level information within that booking. A booking can have multiple items, so one record in my BOOKING table could be linked to multiples in my ITEM table, via an associated reference.

What I need to produce is a chart showing how many bookings have 1 item, how many have 2 etc.

Any ideas?

Thanks,

Rory.

[Anonymous]

Hi,

depending on your data structure you can use a bar chart or straight table with dimension BOOKING and with an expression Count(Items).

better for performance would be:

Sum(ItemCount)

For the last expression create a new field in your item table:

1 as ItemCount

But as I said this depends on your data structure. Please provide a sample app or a screenshot of your data model, this would help.

Best regards

Stefan

[#3]

Hi Stefan,

I'll try and produce a small example file later and post. In response to your reply though - that would work if I want to get the number of items per booking. What I want to get is the number of bookings per number of items:

BOOKING:

BOOKINGID, DETAILID

1, A

2, B

3, C

4, D

5, E

6, F

ITEMS:

DETAILID, DESC

A, ITEM 1

B, ITEM 2

B, ITEM 3

C. ITEM 4

D, ITEM 5

D, ITEM 6

E, ITEM 7

F, ITEM 8

F, ITEM 9

F, ITEM 10

So would expect results like:

No Of Items, No Of Bookings

1, 3 (Being Bookings 1, 3 and 5)

2, 2 (Being Bookings 2 and 4)

3, 1 (Being Booking 6)

As I say, will put up a small example file later.

Thanks,

Rory

[nikhilgandhi715]

Hi Rory,

Attached is a sample file as per the data you have mentioned above. I am not sure if this is what your requirement is; howver tried something. Hope so it helps.

Regards,

Nikhil.

[Anonymous · Accepted solution]

Hi,

see attached qvw. Hope this helps!

Best Regards

Stefan

[MarcoWedel]

Hi,

one solution could be also:

hope this helps

regards

Marco

[MarcoWedel]

or as tables:

hope this helps as well

regards

Marco