topic Re: How to calculate maximum month for each quarter and for year from the available data set in App Development

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

SAMJB — Fri, 13 May 2022 07:20:52 GMT

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.

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

Taoufiq_Zarra — Fri, 13 May 2022 08:14:14 GMT

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

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

vinieme12 — Fri, 13 May 2022 08:22:12 GMT

flag the latest month within each quarter in your load script

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

SAMJB — Fri, 13 May 2022 08:36:10 GMT

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

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

MayilVahanan — Fri, 13 May 2022 10:19:01 GMT

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.

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

SAMJB — Fri, 13 May 2022 11:13:28 GMT

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

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

SAMJB — Fri, 13 May 2022 14:39:53 GMT

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)

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

MayilVahanan — Sun, 15 May 2022 02:47:05 GMT

Try like below

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

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

SAMJB — Mon, 16 May 2022 05:21:59 GMT

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

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

SAMJB — Tue, 17 May 2022 08:35:13 GMT

Hi MayilVahanan,

Awaiting for your response.

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

MayilVahanan — Tue, 17 May 2022 08:45:36 GMT

Try like below

For Quarter: Sum({<Gender = {'M'}>}Sales) / Sum(Sales))

For Max month of Quarter: Sum(Aggr(If(Month = Max(Total<Quarter, Year>Month), Sum({<Gender = {'M'}>}Sales) / Sum(Sales)), Quarter, Month, Year))

If not works, pls attach the sample file with expected result

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

SAMJB — Tue, 17 May 2022 08:58:30 GMT

Hi Mayil Vahanan,

Thanks for your response.

Below expression i have modified.

Sum(Aggr(If([Fiscal Month] = Max(Total<FiscalQuarter, [Fiscal Year]>[Fiscal Month]),
Count({<Gender = {'M'}>}[PS No]) / Count (distinct [PS No])), FiscalQuarter, [Fiscal Month], [Fiscal Year]))

total percentage is above 100

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

SAMJB — Tue, 17 May 2022 09:16:26 GMT

In Pivot table i want to display headcount and gender wise data only max month of every Quarter for all year

below one is full data in table for selected year

Below sample data in pivot table

below expression I'm using for gender

Count({<Gender={'M'}>}distinct [PS No])/Count(distinct [PS No])

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

MayilVahanan — Tue, 17 May 2022 09:28:52 GMT

In that case, u need to use Avg

Avg(Aggr(If([Fiscal Month] = Max(Total<FiscalQuarter, [Fiscal Year]>[Fiscal Month]),
Count({<Gender = {'M'}>}[PS No]) / Count (distinct [PS No])), FiscalQuarter, [Fiscal Month], [Fiscal Year]))

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

SAMJB — Tue, 24 May 2022 10:01:16 GMT

Hi MayilVahanan,

Thanks for the Expression, It's working fine.

Avg(Aggr(If([Fiscal Month] = Max(Total<FiscalQuarter, [Fiscal Year]>[Fiscal Month]),
Count({<Gender = {'M'}>}[PS No]) / Count (distinct [PS No])), FiscalQuarter, [Fiscal Month], [Fiscal Year]))

I have one more doubt.

With same expression i wont to show max month headcount of the year means Mar month.