Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
madushanfernand
Contributor III
Contributor III

Using Aggr with sum and max

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.

Capture.PNG

5 Replies
ariel_klien
Specialist
Specialist

Hi,

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

Please see the attached file

 

Ariel

madushanfernand
Contributor III
Contributor III

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
Specialist
Specialist

Try

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

Ariel

madushanfernand
Contributor III
Contributor III

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.