Using Aggr with sum and max

[madushanfernand]

Hi All

Could you please someone help me on getting following output.?

i have a sales log file something looks like below. SalesKey is the log history key. Once a change in line has occurred or a new line is entered with corresponding changes and new SalesKey will be updated. For an example,

line no 2 is repeated again in line no 9 in records twice since its Ex-Fac-Date has changed. Like wise line no 4 and line no 7 ( High lighted ). but there CPO value is same. because of the change they have two SalesKey.

I want to get an output like shown in bottom table. 

total sales = sum(SALES)

Criteria : when i select a month, need to get sum sales with having only max SalesKeys.

ex : line  no 2 value should be discarded in sum, instead line no 7 value should be taken in for sum.

total sum for following example should be 68311. herewith attached the  excel file and out put need as well.

 

[ariel_klien]

Hi,

Did you try to identify the last SaleKey in the Script?

Please see the attached file

Ariel

[madushanfernand]

hi Ariel

Thank you for the reply. if i go into detail, could we get an out put like this.

1. if i select the month, total sales would be 68311.

2. if i select first week, total sales would be 28510 and for second week 68311

Your output is correct only if i select a month. 

cant we use something like, (mbut this gives me a wrong answer )

Aggr(sum(SALES),Aggr(max(SalesKey),Buyer,CPO))

basically i want the records of sales values  which have the max SalesKey to be selected,

for any selected period of the time.

[ariel_klien]

Try

sum({<SalesKey={$(= max(aggr(max(SalesKey),CPO)))} >}SALES)

Ariel

[madushanfernand]

Hi Ariel
above expression, it passes only one max value to the set.
how can we pass multiple max values to this set SalesKey?
if it is possible this could be solved.

[Brett_Bleess]

Try the following link, it is to the main page of the Design Blog area, there are hundreds of how to posts in this area, use the search dialog to find things that may help you.  I was not exactly sure what you needed at this point, or I would have tried a few direct ones.  Generally it is best to start with a single term first and go from there.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett