Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Did you mean:
Creator

## Sum on Unique Values in Chart

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.

1 Solution

Accepted Solutions
MVP

May be this:

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

7 Replies
MVP

May be this:

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

or

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

or

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

Master

Hello,

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

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

Cheers

Andrew

Master III

Maybe

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

Creator
Author

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?

MVP

May be this:

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

Creator III

like .....

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

Creator
Author

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

Community Browser