topic Re: How to subtract the Average of each group from latest value? in QlikView https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344689#M620060 <HTML><HEAD></HEAD><BODY><P>Hi Sunny,</P><P></P><P>Thank you for helping me with this <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" /></P><P></P><P>Yeah!! It's working good, how can I select the record (Amount) which is latest in Date and subtract it with your expression.</P><P></P><P>I have no idea how to pick it that record.</P></BODY></HTML> Wed, 19 Apr 2017 14:17:50 GMT mohan_1105 2017-04-19T14:17:50Z How to subtract the Average of each group from latest value? https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344687#M620058 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>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,</P><P><IMG alt="Capture.JPG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/160520_Capture.JPG" style="height: auto;" /></P><P>I tried in many ways to get the answer but I got the wrong answers. I used the following expression.</P><P><STRONG>Aggr(avg(NETAMT),CUSTOMER)</STRONG></P><P><STRONG>Aggr(avg(sum(NETAMT)),CUSTOMER)</STRONG></P><P><STRONG>Avg(Aggr(sum(NETAMT),CUSTOMER))</STRONG></P></BODY></HTML> Wed, 19 Apr 2017 13:59:20 GMT https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344687#M620058 mohan_1105 2017-04-19T13:59:20Z Re: How to subtract the Average of each group from latest value? https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344688#M620059 <HTML><HEAD></HEAD><BODY><P>May be this to find the average</P><P></P><P><STRONG>Avg(TOTAL &lt;Group&gt; Aggr(Sum(NETAMT), Group, DATE))</STRONG></P></BODY></HTML> Wed, 19 Apr 2017 14:09:51 GMT https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344688#M620059 sunny_talwar 2017-04-19T14:09:51Z Re: How to subtract the Average of each group from latest value? https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344689#M620060 <HTML><HEAD></HEAD><BODY><P>Hi Sunny,</P><P></P><P>Thank you for helping me with this <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" /></P><P></P><P>Yeah!! It's working good, how can I select the record (Amount) which is latest in Date and subtract it with your expression.</P><P></P><P>I have no idea how to pick it that record.</P></BODY></HTML> Wed, 19 Apr 2017 14:17:50 GMT https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344689#M620060 mohan_1105 2017-04-19T14:17:50Z Re: How to subtract the Average of each group from latest value? https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344690#M620061 <HTML><HEAD></HEAD><BODY><P>May be like this</P><P></P><P><STRONG>FirstSortedValue(Aggr(Sum(NETAMT), Group, DATE), -Aggr(DATE, Group, DATE))</STRONG></P></BODY></HTML> Wed, 19 Apr 2017 14:19:57 GMT https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344690#M620061 sunny_talwar 2017-04-19T14:19:57Z Re: How to subtract the Average of each group from latest value? https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344691#M620062 <HTML><HEAD></HEAD><BODY><P>It isn't working. Output are same as the Amount column.</P></BODY></HTML> Wed, 19 Apr 2017 14:29:06 GMT https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344691#M620062 mohan_1105 2017-04-19T14:29:06Z Re: How to subtract the Average of each group from latest value? https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344692#M620063 <HTML><HEAD></HEAD><BODY><P>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?</P></BODY></HTML> Wed, 19 Apr 2017 14:35:51 GMT https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344692#M620063 sunny_talwar 2017-04-19T14:35:51Z Re: How to subtract the Average of each group from latest value? https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344693#M620064 <HTML><HEAD></HEAD><BODY><P>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.</P></BODY></HTML> Thu, 20 Apr 2017 04:45:32 GMT https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344693#M620064 mohan_1105 2017-04-20T04:45:32Z Re: How to subtract the Average of each group from latest value? https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344694#M620065 <HTML><HEAD></HEAD><BODY><P>I think your date field is not correctly read as date by Qlik Sense.... you can try this as a work around</P><P><STRONG>FirstSortedValue(TOTAL &lt;Group&gt; Amount, -Date#(ORDERDATE, 'D/M/YYYY'))</STRONG></P><P></P><P>but I suggest fixing your <STRONG style="font-size: 13.3333px;">ORDERDATE</STRONG> in the script</P><P><A href="https://community.qlik.com/qlik-blogpost/2954">Why don’t my dates work?</A></P><P><A href="https://community.qlik.com/qlik-blogpost/2622">Get the Dates Right</A></P></BODY></HTML> Thu, 20 Apr 2017 08:56:15 GMT https://community.qlik.com/t5/QlikView/How-to-subtract-the-Average-of-each-group-from-latest-value/m-p/1344694#M620065 sunny_talwar 2017-04-20T08:56:15Z