Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
musketeers
Creator
Creator

Display data for only last month of quarter

Hi, I have data like below:

LOAD * Inline [
Product, Year_Month, FY_Qtr, Sales
P1, 202201, 2022Q1, 25000
P1, 202201, 2022Q1, 25000
P1, 202201, 2022Q1, 100
P1, 202201, 2022Q1, 200
P1, 202201, 2022Q1, 300
P1, 202201, 2022Q1, 400
P1, 202201, 2022Q1, 600
P1, 202202, 2022Q1, 800
P1, 202202, 2022Q1, 350
P1, 202202, 2022Q1, 26500
P1, 202202, 2022Q1, 26500
P1, 202202, 2022Q1, 26500
P1, 202202, 2022Q1, 26500
P1, 202203, 2022Q1, 760
P1, 202203, 2022Q1, 761
P1, 202203, 2022Q1, 762
P1, 202203, 2022Q1, 763
P1, 202203, 2022Q1, 26000
P1, 202203, 2022Q1, 26000
P1, 202203, 2022Q1, 26000
P1, 202204, 2022Q2, 30000
P1, 202204, 2022Q2, 30000
P1, 202204, 2022Q2, 602
P1, 202204, 2022Q2, 504
P1, 202204, 2022Q2, 606
P1, 202204, 2022Q2, 608
P1, 202204, 2022Q2, 609
P1, 202205, 2022Q2, 810
P1, 202205, 2022Q2, 811
P1, 202205, 2022Q2, 36500
P1, 202205, 2022Q2, 26500
P1, 202205, 2022Q2, 26500
P1, 202205, 2022Q2, 26500
P1, 202206, 2022Q2, 759
P1, 202206, 2022Q2, 758
P1, 202206, 2022Q2, 757
P1, 202206, 2022Q2, 755
P1, 202206, 2022Q2, 21000
P1, 202206, 2022Q2, 21000
P1, 202206, 2022Q2, 21000
]

and the output I need is as below:

Product Year_Month Monthly Max Sales Qtrly Sales
P1 202201 25000  
P1 202202 26500  
P1 202203 26000 26000
P1 202204 30000  
P1 202205 36500  
P1 202206 21000 21000

 

Monthly Max Sales column is Max of sales for that month. For the Qtrly Sales, I need to display the max sales for last month of quarter. The expression I am using is as below:

if(wildmatch(MonthEnd(Date(Year_Month,'YYYYMM')) ,
aggr(nodistinct max( MonthEnd(Date(Year_Month,'YYYYMM'))), FY_Qtr)),
alt(aggr(nodistinct Max(Sales), FY_Qtr),0)
)

This expression is giving me the max sales for all the month in that quarter. I need the max sales for the last month of that quarter. Please guide me how to acheive this.

Labels (2)
1 Solution

Accepted Solutions
Ahidhar
Creator III
Creator III

try this

Max Sales :- Sum(aggr(max(Sales),Year_Month))

Last Month Quarter Sales :- if(month(Year_Month)=month(QuarterEnd(Year_Month)),Sum(aggr(max(Sales),Year_Month)),0)

Ahidhar_0-1702971615914.png

 

 

View solution in original post

2 Replies
Ahidhar
Creator III
Creator III

try this

Max Sales :- Sum(aggr(max(Sales),Year_Month))

Last Month Quarter Sales :- if(month(Year_Month)=month(QuarterEnd(Year_Month)),Sum(aggr(max(Sales),Year_Month)),0)

Ahidhar_0-1702971615914.png

 

 

musketeers
Creator
Creator
Author

Hi Ahidhar, Thanks very much for your help. This has worked for me.