Hello,
I have been reading through the various entries on how to filter/aggregate data based on the last/greatest date entry but I still can't seem to get it to work and need some help from the community.
Data
Collab.Country ISO Code | Collab.Supply Plan Bucket | Item.L5 | [SupplyPlan.Measure] | [SupplyPlan.Date] | =Sum( {< [SupplyPlan.Measure]={"InventoryCustSite"} >} [SupplyPlan.Quantity] ) |
AF | BUCKET-B | Depot Medroxyprogesterone Acetate 150 mg/ml (1ml) Vial, Intramuscular | InventoryCustSite | 9/1/2017 | 894,175 |
AF | BUCKET-B | Depot Medroxyprogesterone Acetate 150 mg/ml (1ml) Vial, Intramuscular | InventoryCustSite | 6/1/2017 | 260,250 |
AF | BUCKET-B | Depot Medroxyprogesterone Acetate 150 mg/ml (1ml) Vial, Intramuscular | InventoryCustSite | 3/1/2017 | 339,240 |
AO | BUCKET-A | Depot Medroxyprogesterone Acetate 150 mg/ml (1ml) Vial, Intramuscular | InventoryCustSite | 3/1/2019 | 4,028,526 |
AO | BUCKET-A | Depot Medroxyprogesterone Acetate 150 mg/ml (1ml) Vial, Intramuscular | InventoryCustSite | 12/1/2018 | 23,027,010 |
AO | BUCKET-A | Depot Medroxyprogesterone Acetate 150 mg/ml (1ml) Vial, Intramuscular | InventoryCustSite | 9/1/2018 | 57,716,148 |
Desired
Collab.Country ISO Code | Collab.Supply Plan Bucket | Item.L5 | [SupplyPlan.Measure] | [SupplyPlan.Date] | =Sum( {< [SupplyPlan.Measure]={"InventoryCustSite"} >} [SupplyPlan.Quantity] ) |
AF | BUCKET-B | Depot Medroxyprogesterone Acetate 150 mg/ml (1ml) Vial, Intramuscular | InventoryCustSite | 9/1/2017 | 894,175 |
AO | BUCKET-A | Depot Medroxyprogesterone Acetate 150 mg/ml (1ml) Vial, Intramuscular | InventoryCustSite | 3/1/2019 | 4,028,526 |
Attempted Expression:
=Sum(
{<
[SupplyPlan.Date]={"(=RangeMax([SupplyPlan.Date]))"},
[SupplyPlan.Measure]={"InventoryCustSite"}
>}
[SupplyPlan.Quantity]
)
However the result is no rows at all. What am I missing?
thanks!