Calculate Prior Month

[nicanova]

I've tried several solutions to this from other Community posts, but there must be something I'm missing. I am calculating attrition and need to reference the prior month's headcount.
In the Load Script, I used a crosstable etc. to get to the following 2 fields:

Date(Date#([Headcount MonthYear],'MM/DD/YYYY'),'MMM-YYYY') AS [Headcount MonthYear],
[Headcount]

And using these I can accurately display the total headcount by MonthYear. Now, in a table (later a chart), I want to validate a Prior Month Headcount that I can use in formulas.
I've tried this variable: vPreviousMonth=Date(addmonths(max([Headcount MonthYear]),-1),'MMM-YYYY') 
And each of the following measures:

sum({$<[Headcount MonthYear] ={'$(=$(vPreviousMonth))'}>}[Headcount])
sum({$<[Headcount MonthYear] = {'$(vPreviousMonth)'}>}[Headcount])
Sum({$<[Headcount MonthYear]={"$(=Date(AddMonths(Max(Date),-1),'MM-YYYY'))"}>}[Headcount])

Appreciate any help. Thank you!

[nicanova]

Update: I did get the vPreviousMonth to populate in the table correctly using:
Date(addmonths(([Headcount MonthYear]),-1))

Now I have a table with [Headcount MonthYear] (Sept-2021) and associated [Headcount] and vPreviousMonth (Aug-2021) but no Headcount for the prior month.

[ajaykakkar93]

hi,

can you make a table with these columns [Headcount MonthYear] & sum([Headcount]) 
& provide a screenshot for the same it can be because of month &  year format 

solution :
1) create a new column :

floor([Headcount MonthYear]) as [Headcount MonthYear Num]

2) update the variable with 
floor(Date(addmonths(([Headcount MonthYear]),-1))) 

3) change the measure to
sum({$<[Headcount MonthYear Num] ={'$(=$(vPreviousMonth))'}>}[Headcount])

Hope this helps.

Thank You;

[nicanova]

Hi - Thanks for your help! That measure returned the same month's headcount, picture below:

For Prior Month, I made that column date($(vPreviousMonth),'MMM-YYYY') because without the date format it returned the date in numbers.

[ajaykakkar93]

hi,
follow the below steps example i have attached as qlik sense app file 

One of the solution is:

data:

//step 1
load JDate,EmpID,
MonthName(JDate) as Headcount_MonthYear;
load * Inline [
JDate,EmpID
01/05/2022,1
01/05/2022,2
01/05/2022,3
01/05/2022,4
01/05/2022,5
01/02/2022,2
01/02/2022,3
01/02/2022,4
01/02/2022,5
01/02/2022,7
01/02/2022,8
01/03/2022,1
01/03/2022,2
01/03/2022,3
01/03/2022,5
01/03/2022,7
01/03/2022,8
01/03/2022,9
01/04/2022,4
01/04/2022,50
01/04/2022,70
01/04/2022,80
];

data2:
//step 2 : convert it to number
load JDate,Headcount_MonthYear,Headcount,
Previous(Headcount) as Prev_Headcount;
load
//Autonumber(Headcount_MonthYear) as CalendarKey,
JDate,Headcount_MonthYear,
Count(EmpID) as Headcount
Resident data
Group by JDate,Headcount_MonthYear
Order by
JDate asc,
Headcount_MonthYear asc;

drop Table data;

exit Script;

[nicanova · Accepted solution]

Hi - I just wanted to circle back, I was able to accomplish this leveraging part of your solution, although simpler. I  already had a crosstable to take whether someone should 'count' in a given month and create totals for each month as [Headcount], then had this script, leveraging your suggestions for Previous(Headcount):

Load
[Empl ID],
Date(Date#([Headcount MonthYear],'MM/DD/YYYY'),'MMM-YYYY') AS [Headcount MonthYear],
[Headcount],
Previous([Headcount]) AS [PrevHC]