Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this
FirstSortedValue({<Manufacturer={'Me'}>}Aggr(Sum({<Manufacturer={'Me'}>}Amount), Material, Customer, Manufacturer, Date), -Aggr(Date, Material, Customer, Manufacturer, Date))
Try this
FirstSortedValue({<Manufacturer={'Me'}>}Amount, -Date)
Or this
FirstSortedValue({<Manufacturer={'Me'}>}Aggr(Sum({<Manufacturer={'Me'}>}Amount), Date), -Date)
This is a good idea, but it does not work if I added one more sale line,
M1, C1, Me, 4/1/2017, 10
Try this
FirstSortedValue({<Manufacturer={'Me'}>}Aggr(Sum({<Manufacturer={'Me'}>}Amount), Material, Customer, Manufacturer, Date), -Aggr(Date, Material, Customer, Manufacturer, Date))
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))