topic Re: Sum on Unique Values in Chart in QlikView

Sum on Unique Values in Chart

naziralala — Wed, 06 Jul 2016 20:45:33 GMT

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 )

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.

Re: Sum on Unique Values in Chart

sunny_talwar — Wed, 06 Jul 2016 21:00:25 GMT

May be this:

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

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

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

Re: Sum on Unique Values in Chart

effinty2112 — Wed, 06 Jul 2016 21:02:19 GMT

Hello,

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

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

Cheers

Andrew

Re: Sum on Unique Values in Chart

robert_mika — Wed, 06 Jul 2016 21:02:38 GMT

Maybe

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

Re: Sum on Unique Values in Chart

naziralala — Wed, 06 Jul 2016 22:02:41 GMT

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.

Re: Sum on Unique Values in Chart

sunny_talwar — Wed, 06 Jul 2016 22:06:24 GMT

May be this:

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

Re: Sum on Unique Values in Chart

vikramv — Wed, 06 Jul 2016 22:28:01 GMT

like .....

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

Re: Sum on Unique Values in Chart

naziralala — Thu, 07 Jul 2016 16:31:33 GMT

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