topic Tricky Average! in QlikView

Tricky Average!

Anonymous — Thu, 23 Mar 2017 14:04:11 GMT

Hi Experts!

I'm trying to do some enhancement to the solution I mentioned here:Simple Average Problem

I now have extra data which is now concatenated to the same table in the Simple Average Problem.

Showing the average against ID was easy but I now want to be able to show last three date sale average against Service Order, Service Name and Future Dates. Is this possible?

The resultant straight table should look like this:

I'm attached the data and the app if anyone wants to give it a try. Thanks

Re: Tricky Average!

sunny_talwar — Thu, 23 Mar 2017 14:36:52 GMT

May be this

=Aggr(Avg(TOTAL <ID> Aggr(If(Rank(Date) < 4, Sale), ID, Date)) , ID, ServiceOrder, FutureDates)

Re: Tricky Average!

Anonymous — Thu, 23 Mar 2017 15:26:07 GMT

Hey Sunny thanks for your help. This is exactly what I wanted. The only thing is that as soon as I make a selection, all calculations disappear.

I want to be able to make service order selection and be able to see the relevant calculations as well.

Re: Tricky Average!

sunny_talwar — Thu, 23 Mar 2017 15:28:46 GMT

Try this:

=Aggr(Avg({<ServiceOrder>}TOTAL <ID> Aggr(If(Rank(Only({<ServiceOrder>}Date)) < 4, Only({<ServiceOrder>}Sale)), ID, Date)) , ID, ServiceOrder, FutureDates)