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.

[stevedark]

You've a number of things going on here.  First of all there was a syntax error in the Set Analysis, you were missing some brackets.  It would need to read like this:

Sum({$<Months={$(=Max(Months)-1)}>}Alloactions)

The next problem is that when comparing to a month field in Set Analysis it needs to match to the text rather than the number of the month.  By putting the -1 on there you are forcing the output to an integer.  This is fixable, perhaps with a pick statement, selecting the month name from a list.

This would not actually solve the problem though, as the dollar sign expansion is evaluated outside of the chart, so would always return December, regardless of the column it was in.

You could achieve what you are after by having an extra dimension table that links current and prior months together, as I describe in this blog post

It may be simpler though to go for a straight table with 24 expressions, rather than a pivot.  You would then need to create each column with set analysis, eg:

Count({<Months={'Jan'}>} Employee)

and

Sum({<Months={'Jan'}>} Allocations)

You would then, potentially want to show and hide columns under properties, based on the selections in Months.

Hope that gives some pointers.

[#3]

Hi!

So I should create a straight table with expressions for each month?

But is there really no opportunity to create a pivot with the expr?

Sorry for so many questions, I'm still a newbie

[mohammadkhatimi]

Hope this will help you....

Check the attach file...

Regards,

Mohammad

[sunilkumarqv]

Hi Diana

yes you can't create it in pivot table

create a straight table and use expressions.

[stevedark]

There is usually a way of achieving most things.  The straight table with expressions is probably your best approach though.

If I had to have a pivot I would look at joining the previous months Allocations to the next months staff count.  The methods explained in the blog post I put a link to would make this possible.  This would involve creating a table like this:

Month,Type,Compare Month

Jan,Staff,Jan

Feb,Staff,Feb

Jan,Allocaction,Feb

Mar,Staff,Mar

Feb,Allocation,Mar

You could then use the Compare month as the dimension for the pivot.  This would join in the data model between both the actual month and the prior month in the data model.  If you kept the same expressions you would double count both staff and allocations for each month, but with a little extra set analysis you could pull the right figures:

Count({<Months={'Jan'},Type={'Staff'}>} Employee)

and

Sum({<Months={'Jan'},Type={'Allocation'}>} Allocations)

I've not tried this out, so it may need tweaking, but I believe it would work.

Steve