topic Re: Using Aggr with sum and max in QlikView

Using Aggr with sum and max

madushanfernand — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Using Aggr with sum and max

ariel_klien — Mon, 23 Sep 2019 10:14:28 GMT

Hi,

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

Please see the attached file

Ariel

Re: Using Aggr with sum and max

madushanfernand — Mon, 23 Sep 2019 11:26:45 GMT

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.

Re: Using Aggr with sum and max

ariel_klien — Mon, 23 Sep 2019 11:45:29 GMT

Try

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

Ariel

Re: Using Aggr with sum and max

madushanfernand — Mon, 23 Sep 2019 12:26:11 GMT

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.

Re: Using Aggr with sum and max

Brett_Bleess — Tue, 01 Oct 2019 21:51:28 GMT

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