How to subtract the Average of each group from latest value?

[mohan_1105]

Hi,

I am trying to calculate the average sales amount of each group and this is subtracted from the latest bill amount. Like I showed below in the image,

I tried in many ways to get the answer but I got the wrong answers. I used the following expression.

Aggr(avg(NETAMT),CUSTOMER)

Aggr(avg(sum(NETAMT)),CUSTOMER)

Avg(Aggr(sum(NETAMT),CUSTOMER))

[sunny_talwar]

May be this to find the average

Avg(TOTAL <Group> Aggr(Sum(NETAMT), Group, DATE))

[mohan_1105]

Hi Sunny,

Thank you for helping me with this

Yeah!! It's working good, how can I select the record (Amount) which is latest in Date and subtract it with your expression.

I have no idea how to pick it that record.

[sunny_talwar]

May be like this

FirstSortedValue(Aggr(Sum(NETAMT), Group, DATE), -Aggr(DATE, Group, DATE))

[mohan_1105]

It isn't working. Output are same as the Amount column.

[sunny_talwar]

Do you have both Group and DATE as your chart dimensions where you are trying this? or do you just have Group as a dimension?

[mohan_1105]

Yeah, Both Group and vDATE are in dimensions and I am trying to find latest sales made by each group is above their respective group average. Also, sample file is attached.

[sunny_talwar · Accepted solution]

I think your date field is not correctly read as date by Qlik Sense.... you can try this as a work around

FirstSortedValue(TOTAL <Group> Amount, -Date#(ORDERDATE, 'D/M/YYYY'))

but I suggest fixing your ORDERDATE in the script

Why don’t my dates work?

Get the Dates Right