Announcements
cancel
Showing results for
Did you mean:
Partner - Creator III

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

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))

1 Solution

Accepted Solutions
MVP

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

7 Replies
MVP

May be this to find the average

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

Partner - Creator III
Author

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.

MVP

May be like this

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

Partner - Creator III
Author

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

MVP

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?

Partner - Creator III
Author

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.

MVP

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

Community Browser