How to compare two dimensions with aggr function

[Anonymous]

Hi there,

I need some help..

I’ve got an database, where appointments of several types are made on multiple locations.

Each appointment type can require different type of rooms (even multiple rooms)

Each location has several rooms of each type.

So kind of like this (oversimplified):

AppointmentsTable:

-AppID

-AppTypeID

-LocationID

RoomType:

-RoomTypeID

-AppTypeID

Location:

-LocationID

-RoomTypeID

-CapacityOfRoomType

Now: If I load this in QV, I get a circular reference of course. So, I’ve renamed the ID of RoomType to RoomTypeID2 – which worked obviously.

But the problem is I can’t get the query right where I want to show the used Rooms vs the capacity.

I want to show:

RoomTypeID – Count(Appointments) – Count(Capacity)

A1 – 45 – 100

A2 – 50 – 102

And now I don’t know to solve it. Any help?

I’ve tried a aggr function like

SUM(aggr(SUM([CapacityOfRoomType]),[RoomType Id], [RoomType2Id])). But it doesn’t work.

I get:

RoomTypeID2 – Count(Appointments) – Count(Capacity)

A1 – 45 – 202

A2 – 50 – 202

If I include RoomTypeID then it shows all the correct values, So I should be close…

RoomType2ID – RoomTypeID - Count(Appointments) – Count(Capacity)

A1 – A1 – 45 – 100

A1 – A2 – 0 – 102

A2 – A1 – 50 – 100

A2 – A2 – 0 – 102

I tried something like:

SUM(${<[RoomType2Id]]=[RoomType Id]>}aggr(SUM(${<[RoomType2 Id]]=[RoomType Id]>}[CapacityOfRoomType]),[RoomType Id], [RoomType2Id])). But it doesn’t work.

I hope someone can help me by:

1. a) Solving the circular reference by adjusting my data-model
2. b) Correct the function or replace it somehow.

Thanks!

[sunny_talwar]

How about this for a quick fix

Sum(Aggr(Sum(If([RoomType Id] = [RoomType2Id], [CapacityOfRoomType])),[RoomType Id], [RoomType2Id]))

For a longer term fix may be create a better data model. It would be difficult to propose anything without looking at what you have today. Would you be able to share a sample?

[Anonymous]

Sunny, Thanks, but it doesn't work. I've included a sample, maybe you can have a look ? Thanks a lot.

And yes, I'm eager to have the longer term fix

[sunny_talwar]

How about this?

Count(Aggr(If(RoomType_ID = RoomType2_ID, NrOfRooms),RoomType_ID, RoomType2_ID, NrOfRooms))

[Anonymous]

It looks allright, but it isn't unfortunately. If you add more rooms, you see that it doesn't work.

And I made a slight error in using Count instead of Sum.

See attached qvf.

----

LocationRooms:

LOAD * Inline [Location_ID, RoomType_ID, NrOfRooms

  L1, R1, 2

    L1, R2, 1

    L2, R1, 1

    L3, R1, 5

    L3, R2, 10

    ];

Thanks for your help!

[brunobertels]

Hi

The fact you get a circular reférence is not a problem.

have a look here for further info about circular reference

https://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

So first i would try with a circular reference

Another way could be to join (leftjoin) the appointement table and the location table

[Anonymous]

For this sample file, the circular reference is indeed not the problem. Well spotted! But for my actual database, with over 10 tables, it is a problem. So I have to find a way to correct the circular reference, somehow as stated in the excellent example you posted (thanks!).

So indeed I need two keys: RoomType_ID and RoomType2_ID. Now I have to find the correct function to get both results in one table..

Thanks

[sunny_talwar]

How about this

Dimension

RoomType_ID

Expressions

1) Sum(Aggr(Count(If(RoomType2_ID = RoomType_ID, Appointment_ID)), RoomType2_ID, RoomType_ID, Appointment_ID))

2) Sum(NrOfRooms)

[Anonymous · Accepted solution]

Sunny,

It works for the sample database, but not for my production one. But, I switched the Count and If statement, and now it works for my database as well:

Sum(Aggr(If(RoomType2_ID = RoomType_ID, Count(Appointment_ID)), RoomType2_ID, RoomType_ID, Appointment_ID))

So thanks a lot!!

[sunny_talwar]

Awesome, I am glad I was able to put you in the right direction. Since you have been able to resolve the issue, I would suggest you to mark your response as correct and mark any helpful responses to close down this thread.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny