topic Re: Current vs Previous Month expression in QlikView

Current vs Previous Month expression

Tue, 05 May 2015 11:59:36 GMT

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.

Re: Current vs Previous Month expression

sunny_talwar — Tue, 05 May 2015 12:06:54 GMT

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

Re: Current vs Previous Month expression

Tue, 05 May 2015 12:20:35 GMT

it's not working

Re: Current vs Previous Month expression

sunny_talwar — Tue, 05 May 2015 12:23:25 GMT

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

Sunny

Re: Current vs Previous Month expression

Tue, 05 May 2015 12:25:26 GMT

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

Re: Current vs Previous Month expression

Tue, 05 May 2015 12:29:13 GMT

Nope, it's not what is required

Re: Current vs Previous Month expression

Tue, 05 May 2015 12:33:40 GMT

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

Re: Current vs Previous Month expression

mohammadkhatimi — Tue, 05 May 2015 12:35:17 GMT

hope this helps you:-

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

Regards

Mohammad

Re: Current vs Previous Month expression

JustinDallas — Tue, 05 May 2015 12:47:15 GMT

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.

Re: Current vs Previous Month expression

buzzy996 — Tue, 05 May 2015 13:01:18 GMT

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)

Re: Current vs Previous Month expression

Tue, 05 May 2015 13:26:37 GMT

Unfortunately this doesnt work either Nobody knows how to calculate this difference?

Re: Current vs Previous Month expression

Tue, 05 May 2015 13:27:55 GMT

My first dimension is Business group, and the second one is 'Months'. I dont have MonthYear or Year or anything else

Re: Current vs Previous Month expression

sunny_talwar — Tue, 05 May 2015 13:28:28 GMT

Diana‌ we all are providing you the solution based on what we think your fields, formats etc may look like. This is the best we probably can do without looking at your application.

Best,

Sunny

Re: Current vs Previous Month expression

sasikanth — Tue, 05 May 2015 13:32:23 GMT

try some thing like below

count(${<Month={$(=max(Month))}>}employee)-count({<Month={$(=Max(Month)-1)}>}employee)

Re: Current vs Previous Month expression

sunny_talwar — Tue, 05 May 2015 13:34:26 GMT

If Month is one of your dimensions then I wouldn't probably use set analysis. Above/Below function if a straight table or After/Before function if pivot table (and months are laid out horizontally) will be required. But again the ideal solution can only be provided if and when you can provide some data.

HTH

Best,

Sunny

Re: Current vs Previous Month expression

santhoo_san — Tue, 05 May 2015 13:47:33 GMT

Create a monthend dateID use it in the set analysis expression

count({<DateID={$(=max(DateID))},Month=>}employee) - count({<DateID={$(=max(DateID)-1)},Month=>}employee)

hope this helps.

Re: Current vs Previous Month expression

nico_ilog — Tue, 05 May 2015 22:45:57 GMT

Hi Diana,

Create 2 variables. (Ctrl + Alt + V --> Add)

vMaxMonth = Month(Max(YourDateField))

vMaxMonthPrior = Month(addmonths(Max(YourDateField),-1))

In your graph, create these expressions:

1st Exp for Selected Month =Count({<Month ={'$(vMaxMonth)'}>} Employee)

2nd Exp for Prior Month of selected = =Count({<Month ={'$(vMaxMonthPrior)'}>} Employee)

Hope this helps.

PS: Name the expressions

1 = Headcount $(vMaxMonth)

2 = Headcount $(vMaxMonthPrior)

Regards,

Re: Current vs Previous Month expression

stevedark — Tue, 05 May 2015 23:07:54 GMT

Hi Diana,

You will need to tweak things to match the fields you have in your model, but the app I uploaded to Qlik Community should give you a good head start on this:

QlikView App: Set Analysis - Prior Period Comparison

There are also some notes on how the app works which may be useful.

Steve

Re: Current vs Previous Month expression

Wed, 06 May 2015 06:25:26 GMT

Is your Month column just a text column with names of months or is it a Month converted Date column (like =month(Date))?

Re: Current vs Previous Month expression

Wed, 06 May 2015 06:36:22 GMT

These formulas arent working, here is my sample data attached.

Please help!