Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hytown2000
Partner - Contributor III
Partner - Contributor III

Refer an aggregated date in set expression or sumif

Hi gurus,

I have a challenge to use an aggregated date Max(Date) in another formula to calculate Sales Amount for that aggregated date.

Last Sold Date is defined as Max(Date)

My-Sales LSD is defined as Sum({<Date={'$(=max(Date))'},Manufacturer={'Me'}>}Amount)

Last Sold Date is working fine, but My-Sales LSD is wrong, it takes the Max(Date) for the whole set, instead of for each Material, I understand why it happens, as expression in set analysis evaluates the whole chart. I tried Sum(If...) and it is the same.

Last Sold Date.jpg

1 Solution

Accepted Solutions
sunny_talwar

Try this

FirstSortedValue({<Manufacturer={'Me'}>}Aggr(Sum({<Manufacturer={'Me'}>}Amount), Material, Customer, Manufacturer, Date), -Aggr(Date, Material, Customer, Manufacturer, Date))

View solution in original post

5 Replies
sunny_talwar

Try this

FirstSortedValue({<Manufacturer={'Me'}>}Amount, -Date)

sunny_talwar

Or this

FirstSortedValue({<Manufacturer={'Me'}>}Aggr(Sum({<Manufacturer={'Me'}>}Amount), Date), -Date)

hytown2000
Partner - Contributor III
Partner - Contributor III
Author

This is a good idea, but it does not work if I added one more sale line,

M1, C1, Me, 4/1/2017, 10

sunny_talwar

Try this

FirstSortedValue({<Manufacturer={'Me'}>}Aggr(Sum({<Manufacturer={'Me'}>}Amount), Material, Customer, Manufacturer, Date), -Aggr(Date, Material, Customer, Manufacturer, Date))

hytown2000
Partner - Contributor III
Partner - Contributor III
Author

This works like a charm, later I found it returns null if there are multiple customers on the same date, but I managed to solve it using your idea with the below formula.

FirstSortedValue(Aggr(Sum({<Manufacturer={'Me'}>}Amount),Date,Material),-Aggr(Date,Date,Material))