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.
