HeadCount Calculation

[anat]

	Month	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	Jan-15	Feb-15	Mar-15
Total HC		1	6	11	16	21	26	31	36	41	46	51	56

if i select Year 2014-15 it should display year end month data(Mar-15)= 56

same if i select Q1 then output = 11(Q1 end month :June-14 data)

for Q2  output= 26(Q1 end month :Sep-14 data)

for Q3 then output= 41(Q1 end month dec-14 data)

if i select Q4 then output= 56(Q1 end month :Mar-15 data)

Q1=Apr-14 to Jun-14

Q2=Jul-14 to Sep-14

Q3=Oct-14 to  Dec-14

Q4-Jan-15 to Mar-15  and  Year=2014-15

[ajsjoshua]

Hi,

check this

YTD (Year-To-Date) Sales:

Sum({$<MonthID = {“<=$(=Max(MonthID))”},

Year = {$(=Max(Year))},

Quarter = ,

Month = >} Sales)

QTD  (Quarter-To-Date) Sales:

Sum({$<MonthID = {“<=$(=Max(MonthID))”},

QuarterID = {$(=Max(QuarterID))},

Year = ,

Quarter = ,

Month = >} Sales)

MTD (Month-To-Date) Sales:

Sum({$<MonthID = {$(=Max(MonthID))},

Year = ,

Quarter = ,

Month = >} Sales)

[chetansehgal]

Hi Ananth,

If you are always selecting a Year or Qtr then the following formula will always give you the sales of the Max month of selected(current) Year or Qtr.  Also you need to create the Qtr field based on the fiscal year in the calendar script.

=Sum({<MonthID={$(=Max(MonthID))>}Sales)

or you can try below formula

=Sum({<Month={$(=Month(Max(Date)))}>}Sales)

But above formulas will give 0 if there is no entry for the latest month.

Thanks,

Chetan