topic Re: Function to SUM totals for Previous YYYYMM in QlikView

Function to SUM totals for Previous YYYYMM

Qlik__Newbie — Thu, 24 Oct 2024 14:35:05 GMT

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

Re: Function to SUM totals for Previous YYYYMM

Kushal_Chawda — Thu, 24 Oct 2024 16:02:02 GMT

@Qlik__Newbie try below expression

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

Re: Function to SUM totals for Previous YYYYMM

Qlik__Newbie — Thu, 24 Oct 2024 18:25:11 GMT

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

Re: Function to SUM totals for Previous YYYYMM

marksouzacosta — Thu, 24 Oct 2024 18:33:00 GMT

Yes, exactly.

Re: Function to SUM totals for Previous YYYYMM

Kushal_Chawda — Thu, 24 Oct 2024 19:12:09 GMT

@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()

Re: Function to SUM totals for Previous YYYYMM

Qlik__Newbie — Thu, 24 Oct 2024 19:22:26 GMT

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

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

Re: Function to SUM totals for Previous YYYYMM

Vegar — Fri, 25 Oct 2024 03:15:01 GMT

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.

Re: Function to SUM totals for Previous YYYYMM

Qlik__Newbie — Fri, 25 Oct 2024 09:21:52 GMT

Thanks guys all sorted now