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

[lorenzoconforti]

In your set expression the value of Max is calculated only once

[ghaliabed]

Yes true, if there is a way to make it recalculate for every row or cell of the chart would resolve my issue i believe, but I do not know if that is possible with Qlik 

If i take same expression and place it as a measure, it returns the correct value per Quarter or Year, but when placed in a set expression yes it only returns the global max outside context of the chart 

[lorenzoconforti]

Please remind me which tabs in the dashboard I should be looking at and what result do you expect exactly

[ghaliabed]

Yes Sorry

So sheet named: My new sheet

The bottom chart holds the calculation i was mentioning.

Now there if i only have Year --> Month OR Quarter --> Month

The calculation done is fine except for the Total when i have the Quarter expanded, it just shows ZEROs.

Main goal for these is to be able to replace the current Row dimension (EntityBase Level) with anything else and have calculation still work, where only the latest month value is being shown per Column grouping.

[lorenzoconforti]

Try this

if
(
     Dimensionality() = 1
  	,sum(SignedData) // Month Level
    ,FirstSortedValue(aggr(sum(SignedData),  [Year Month Number] ),-aggr(only([Year Month Number]) , [Year Month Number]))
)

[ghaliabed]

I modified the measure just to handle having Year --> Quarter --> Month and made it like this

if
(
    Dimensionality() = 2
    ,sum(SignedData) // Month Level
    ,FirstSortedValue(aggr(sum(SignedData),  [Year Month Number] ),-aggr(only([Year Month Number]) , [Year Month Number])) // Year and Quarter Level
)

So this works if i have only one value in the Rows selected, so example if i select Entity Base Level MASIA i get the following which is correct:

But if i don't have any selections i get this:

[lorenzoconforti]

In that case dimensionality is still 1 as it's linked to the rows

if you wanted to explore column dimensionality you need to use secondarydimensionality but I don't believe you need it in your case