Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Did you try to identify the last SaleKey in the Script?
Please see the attached file
Ariel
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.
Try
sum({<SalesKey={$(= max(aggr(max(SalesKey),CPO)))} >}SALES)
Ariel
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