Function to SUM totals for Previous YYYYMM

[Qlik__Newbie]

Hi,

I am a Qlik Sense Enterprise newbie btw so take it easy on me.

I have the following small test dataset for a Headcount population by region and country and want to display the sum per region for last YYYYMM using the Month_End field in a bar or pie chart

So say this is 202410 i want to sum for 202409 only.

I should get EMEA 745, APAC 2020 and AMERICAS 348

Can someone help me please, i have tried looking online but cant find an exact solution

MONTH_end	Region	Country	HC
202408	EMEA	Germany	199
202408	EMEA	Norway	180
202408	AMERICAS	Mexico	167
202408	EMEA	Austria	345
202408	APAC	Korea, Republic of	1000
202408	APAC	United Arab Emirates	1020
202408	AMERICAS	Austria	180
202409	EMEA	Germany	197
202409	EMEA	Norway	178
202409	AMERICAS	Mexico	168
202409	EMEA	Austria	370
202409	APAC	Korea, Republic of	1000
202409	APAC	United Arab Emirates	1020
202409	AMERICAS	Austria	180

[Kushal_Chawda · Accepted solution]

@Qlik__Newbie  try below expression

=sum({<MONTH_end= {"$(=date(addmonths(date#(max(MONTH_end),'YYYYMM'),-1),'YYYYMM'))"}>}HC)

[Qlik__Newbie]

Hi. Thats great but gives me 202408 values. How can i get 202409 values based on this month being 202410. Do i change the function taking out the -1 month from max month_end and just have max month_end?

Thanks in advance

[marksouzacosta]

Yes, exactly.

[Kushal_Chawda · Accepted solution]

@Qlik__Newbie  Correct. As you have mentioned previous, I have used addmonths() function to go to previous month. if you want to keep latest month, no need to use addmonths()

[Qlik__Newbie]

Hi. So I just take addonths out and the -1.

I'll try it tomorrow when in work. Thanks for all the help guys

[Vegar]

What if you change the field value to fit your need?

Load

MONTH_end, Region, Country, HC,

Date(Addmonths(date#(MONTH_end, 'YYYYMM'),1),'YYYYMM') AS MONTH_start

From Source;

Then you use MONTH_start to filter or segment your data.

[Qlik__Newbie]

Thanks guys all sorted now