How to calculate maximum month for each quarter and for year from the available data set

[SAMJB]

How to calculate maximum month for each quarter and for year from the available data set

I have Fiscal year, Month, Quarter, Headcount columns, I want to display max month of headcount in every  quarter and  year in table chart.

Can someone help me on this.

[Taoufiq_Zarra]

@SAMJB  do you mean Max(total <Quarter> Value) ?

[vinieme12]

flag the latest month within each quarter in your load script

[SAMJB]

Yes! I want to show Quarter wise max month.

Please tell me the Set Analysis exp for this or what condition need to write for this in script

[MayilVahanan]

HI 

In Front end,  if you don't have access to edit script means, try like below,

Aggr(If(Month = Max(Total<Quarter, Year>Month), Sum(Sales)), Quarter, Month, Year)

Else, In Script, 

MasterCalendar:
LOAD
Year(CalDate) as Year,
WeekDay(CalDate) as WeekDay,
'Q' & Ceil(Month(CalDate)/3) as Quarter,
QuarterName(CalDate) as QuarterName,
Month(CalDate) as Month,
If(Month(CalDate) = Month(QuarterEnd(CalDate)), 1, 0) as MaxMonthFlag,
Date(MonthStart(CalDate), 'MM-YYYY') as MonthYear,
1000*Rand() as Sales;
LOAD
Date($(vMinDate) + IterNo()-1) as CalDate
AutoGenerate 1
While $(vMinDate) + IterNo()-1 <= $(vMaxDate);

Front End: Sum({<MaxMonthFlag={1}>}Sales)

Based on ur requirement, Change based on Fiscal calendar.

[SAMJB]

Hello Mayilvahanan,

Thanks/Nandri for your response.

But for fiscal year 2022-23 we have loaded April month data Q1 that data will not flow if i use above logic.
So i need to display that data also, based on that please help with logic.

for Eg. fiscal year 2021-22 we have Q1 and all three month Apr, May and Jun. in table default jun month data should be display  and based on selection Apr month data also display

[SAMJB]

Below expression is working, But i/m not getting the total. please find the attached screenshot.

Aggr(If(Month = Max(Total<Quarter, Year>Month), Sum(Sales)), Quarter, Month, Year)

[MayilVahanan]

HI 

Try like below

Sum(Aggr(If(Month = Max(Total<Quarter, Year>Month), Sum(Sales)), Quarter, Month, Year))

[SAMJB]

Hi MayilVahanan,

Thanks it's working fine.

Sum(Aggr(If(Month = Max(Total<Quarter, Year>Month), Sum(Sales)), Quarter, Month, Year))

One more doubt please. I want to add Gender = ('M') nd Gender =('F') separate column in percentage. Please help me with this.

 

Regards
SamJB

[SAMJB]

Hi MayilVahanan,

Awaiting for your response.