Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan_1105
Partner - Creator III
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,

Capture.JPG

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
sunny_talwar

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

View solution in original post

7 Replies
sunny_talwar

May be this to find the average

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

mohan_1105
Partner - Creator III
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.

sunny_talwar

May be like this

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

mohan_1105
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
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.

sunny_talwar

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