Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

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

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

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

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

May be this to find the average

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

Partner
Partner

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

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.

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

May be like this

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

Partner
Partner

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

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

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

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
Partner

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

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.

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

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