Sum for the Latest Month across the Years

[ghaliabed]

Hello

Goal:

• Create a Master Measure for Total Number of Employees  to be used against any dimension
• Total Number of Employees is a cumulative value so it is not a direct Sum, ie: it should be the Sum of Employees found in the Latest Month.

Examples below on data and output chart needed.

Currently i tried the below Set Expression:

SUM ({$< [Year Month]={$(=MAX([Year Month]))} >} [Number of Employees])

However, when i place this measure in a bar chart grouped by the Year, only the Latest Year will get a value while the rest will be ZERO.

Ex: if Latest Month in the data is = 2020-08, but knowing i have months in the previous years

Fiscal Year	Total Employee
2019	0
2020	10

Example of Data Present:

Calendar:

Date	Year	Fiscal Year	Year Month
2019-08-01	2019	2019	2019-08
2019-09-01	2019	2019	2019-09
2019-10-01	2019	2020	2019-10

Fact Table:

DateRegionHead Count

Date	Region	Number of Employees
2019-08-01	A	1
2019-08-01	B	1
2019-09-01	A	2
2019-09-01	B	2
2019-09-01	C	2
2019-10-01	A	3
2019-10-01	B	3
2019-10-01	C	3

Target Measure/Chart:

Table Chart that will show this:

Fiscal Year	Total Employee	Logic
2019	6	Sum of the Regions found in Month 2019-09 ; the last month of the Fiscal Year 2019
2020	9	Sum of the Regions found in Month 2019-10 ; the last month of the Fiscal Year 2020

[ghaliabed]

I found a possible way to do this but not fully working yet.

Using the FirstSortedValue function:

FirstSortedValue( Aggr( Sum( Number of Employee) , [Year Month] ), -Num([Year Month] ) )

Great post here:

https://community.qlik.com/t5/Qlik-Design-Blog/Additive-and-Non-Additive-Numbers/bc-p/1687631#M8701

However it still doesn't work fully for my case as when i add dimensions other than Calendar it only gives a value for one of the values but not all of them.

Like image below:

[lorenzoconforti]

You probably need to add the extra dimensions to your aggregation function

Based on the formula in your chart:

FirstSortedValue( Aggr( Sum(SignedData) , [Year Month Number], ACCOUNT_TYPE ), -Num([Year Month Number] ) )

[ghaliabed]

I tried adding it in the Aggr part, but when i did no values were returned at all.

Plus i need this to stay dynamic as in regardless what dimension i add to the chart i want it to show value.

FirstSortedValue( Aggr( Sum( SignedData ) , [Year Month Number], ACCOUNT_TYPE ), -Num([Year Month Number]) )

 

[lorenzoconforti]

Can you post the dashboard or recreate the issue in a new one?

[ghaliabed]

Please find attached the dashboard i am working with

[lorenzoconforti]

This works for the sample table (see attached):

=FirstSortedValue(aggr(sum(SignedData), ACCOUNT_TYPE, [Year Month Number] ),-aggr(only([Year Month Number]) , ACCOUNT_TYPE, [Year Month Number]))

To make it into a master measure you would need to add all the dimensions you'll likely to use in your charts in the two aggregation functions; it should work

[ghaliabed]

It doesn't seem to be working across all cases.

in this example not all Account Types are being calculated and also if you want to show total at level of the chart, it does not show but is just BLANK.

Pick:

FirstSortedValue(aggr(sum(SignedData), ACCOUNT_TYPE, [Year Month Number] ),-aggr(only([Year Month Number]) , ACCOUNT_TYPE, [Year Month Number]))

[lorenzoconforti]

This table shows different dimensions; you need to add them to the formula

[ghaliabed]

yes the dimensions used in the chart already added in the Formula, and was getting results in the image