Sum on Unique Values in Chart

[naziralala]

Hello All,

Here is a new problem as below:

I am trying to Sum the Stay only based on Distinct Booking Code in a chart expression.

The code is below:

sum(  [DISTINCT]<BookingCode> Stay)

But this is giving error in expression.

I also tried using the code as below:

aggr( Distinct sum(Stay), BookingCode )

or

Sum({<BStatus={'RES'}>[Distinct] BookingCode},Stay)

and also

Sum(Aggr(Count(DISTINCT BookingCode),Stay) )

The last one works, but I do not get the desired result.

Can anyone please assist.

Thanks in advance.

[sunny_talwar]

May be this:

Sum(Aggr(Sum(DISTINCT Stay), BookingCode))

or

Sum(Aggr(Avg(Stay), BookingCode))

or

Sum(Aggr(Only(Stay), BookingCode))

[effinty2112]

Hello,

               If there is only one distinct value of Stay for each BookingCode then try this expression:

Sum(AGGR(Only(Stay),BookingCode))

Cheers

Andrew

[robert_mika]

Maybe

Sum(Aggr(sum(DISTINCT BookingCode),Stay) )

[naziralala]

The same Booking code appears multiple times, having same stay. Also, the

bstatus needs to be considered as Res. How can i incorporate that as well?

Please assist.

[sunny_talwar · Accepted solution]

May be this:

Sum(Aggr(Only({<bstatus = {'Res'}>} Stay), BookingCode))

[vikramv]

like .....

Sum(Aggr(sum(DISTINCT {<bstatus={'Res'}>} BookingCode),Stay) )

[naziralala]

Hello,

Can you please assist why the code below is not working correctly?

Sum(Aggr(Max({<bstatus = {'Res'},BookingYear={$(=Max(BookingYear)-1)}>} Cost), BookingCode))

The below works fine though:

Sum(Aggr(Max({<bstatus = {'Res'}>} Cost), BookingCode))

Thanks