Current vs Previous Month expression

[#1]

Hi!

I have an a table like:

Months	Jan
Business Group	FTE	Headcount	Current Month vs Previous Months
A	107.3	117	0
B	5.35	6	0
c	57.82	74	0
d	49.99	55	0

My expression is: Count(Employee)

Does anybody know how to write an expression for calculation of difference Month vs Previous month. For example February vs January?

I used an expression like: Count(Employee)- before(Count(Employee) but it shows me null when a selection of month is made.

[sunny_talwar]

Month isn't one of your dimension in the chart it seems, why don't you use set analysis to do this?

=(Count({<MonthYear = {"$(=MonthName(AddMonths(Max(MonthYear), - 1)))"}>} Employee)/Count({<MontName(MonthYear = {"$(=Max(MonthYear)))"}>} Employee)) - 1

Note: MonthYear is a field that will be created in your load script using MonthName() function.

HTH

Best,

Sunny

[#3]

it's not working

[sunny_talwar]

Can you share a sample. The field names you are using and formats you have might look differently.

Sunny

[#5]

Create the variables:

vMax_Date =Date(Max(Date),'DD/MM/YYYY')

vMin_Date =Date(Min(Date),'DD/MM/YYYY')

v_MTD_Start=Date(Monthstart(vMax_Date),'DD/MM/YYYY')

v_MTD_End=Date((vMax_Date),'DD/MM/YYYY')

v_MTD_LY_Start=Date(MonthStart(AddMonths(vMax_Date,-1)),'DD/MM/YYYY')

v_MTD_LY_End=Date((AddMonths(vMax_Date,-1)),'DD/MM/YYYY')

then, the expression in text box:

='Current Month: ' & Num(count( {<Month=,Date ={">=$(=v_MTD_Start)<=$(=v_MTD_End)"} >} [Employee]),'###0')
& chr(10)
&
'Last Month: '& Num(count( {<Month=,Date ={">=$(=v_MTD_LY_Start)<=$(=v_MTD_LY_End)"} >} [Employee]),'###0')

or break both the expressions and use as two expression in table chart

[#6]

Nope, it's not what is required

[#7]

Now if someone selects February in Month list-box, then it will give the counts related to Feb in current month and

counts related to Jan in previous month

[mohammadkhatimi]

hope this helps you:-

Count(Employee)- (Count({<MonthYear = {"$(=MonthName(AddMonths(Max(MonthYear), - 1)))"}>} Employee)

Regards

Mohammad

[JustinDallas]

This doesn't remotely answer your question, but in the future, you might want to consider using a Timeline table.  It will really make such calculations like this easier.

[buzzy996]

try this,

CM:

sum({$<Year = {$(=only(Year) - $(=if(only(Month) = 1, 1, 0)))}, Month = {$(=month(MonthYear))}>} LineSalesAmount)

PM:

sum({$<Year = {$(=only(Year) - $(=if(only(Month) = 1, 1, 0)))}, Month = {$(=month(addmonths(MonthYear, -1)))}>} LineSalesAmount)