Max month sales of each Year?

This is the data
Product SalesDate SalesAmount
Apple Jan-30-2017 $100
Apple Feb-28-2017 $50
Apple March-30-2017 $200
Apple Jan-30-2018 $200
Apple Feb-28-2018 $100
Apple March-30-2018 $300
Apple April-30-2018 $400
Required Output
Product SalesDate SalesAmount
Apple March-30-2017 $200
Apple April-30-2018 $400

Data:
Load
Product,
date(date#(SalesDate,’MMM-DD-YYYY’)) as SalesDate,
Month(date#(SalesDate,’MMM-DD-YYYY’)) as Month,
Year(date#(SalesDate,’MMM-DD-YYYY’)) as Year,
date(date#(SalesDate,’MMM-DD-YYYY’),’MMM-YYYY’) as MonthYear,
SalesAmount
Inline [
Product,SalesDate,SalesAmount
Apple,’Jan-30-2017′,100
Apple,’Feb-28-2017′,50
Apple,’Mar-30-2017′,200
Apple,’Jan-30-2018′,200
Apple,’Feb-28-2018′,100
Apple,’Mar-30-2018′,300
Apple,’Apr-30-2018′,400
];
Expression:
Sum({<MonthYear={“=aggr(rank(SalesAmount),Product,Year,Month)=1”}>}SalesAmount)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.