Formula Help

[#1]

Hi

I'm trying to write a formula that adds one field, dependant on the value of a 2nd field.

Due to the dataset the two fields contents can be the same in numerous lines of data but I want to only sum up the values once, irrespective of how many times they are duplicated.

Here is a sample of data I have, for which the answer should be 12 (3 bookings of 4 people each)

Booking No People Date

• 1234 4 01/01/10
• 1234 4 02/01/10
• 1235 4 01/01/10
• 1235 4 02/01/10
• 1236 4 01/01/10
• 1236 4 02/01/10

In theory there could be a record for each day for years so I need a formula that will take all the Booking No's only once and then sum the People. I've been playing with various If & Aggr formulas (including the DISTINCT function) as well as some Set Analysis but have been unable to come up with what I need.

I'm sure it's quite simple for most of you out there, but I just can't get it right. Any help greatly appreciated.

Thanks

[tresesco]

You can do it at load time (use DISTINCT load). if you need all data at the front end in other reporting, anyhow you have to tell QV that which value of repeated Bookin No (1st occurance or last occ or so..) you are considering for summing up. That would make it a little complicated; might be you have to use firstsortedvalue() function with aggr() function or so. In that case you can go for a new field creation at the backend using like:

Load DISTINCT Booking Number, People From .....;

Regards, tresesco

[vgutkovsky]

This should work: sum(aggr(sum(distinct People),[Booking No]))

Cheers,

[#4]

Thanks for quick reply.

I'll have a look at DISTINCT Load but it's not something I'm yet familiar with.

Assuming that I'm unable to make this work either (not much of a stretch!) my preferred solution would still be to create an expression with a working formula if any ideas.

Steve...

[#5]

Thanks for reply.

I still need to identify the Booking No's to be included in the calculation based on a values in another field (could be a date, or a booking status or even both), as opposed to including all the records in the dataset. any ideas?

Steve...

[vgutkovsky]

The aggr doesn't ignore chart dimensions. So, for example, if you have a chart that has dimensions date and booking status, the above aggr (as an expression) would take into account only those booking numbers that correspond to the given dimension. Try it, let me know if it doesn't work for some reason.

[#7]

Thanks Vlad.

I don't have the other fields set as dimensions which is why I'm trying to find an expression formula to incorporate the data that needs to be taken into account instead.

The table that I'm trying to incorporate this expression in also has various others expressions based on the existing dimensions so I can't add more dimensions or it will affect my other results.

[vgutkovsky]

I'm having trouble visualizing what you mean. Can you post a reduced example?