topic Sum for the Latest Month across the Years in App Development

Sum for the Latest Month across the Years

ghaliabed — Mon, 20 Dec 2021 21:32:42 GMT

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

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 25 Mar 2020 13:09:28 GMT

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:

Re: Sum for the Latest Month across the Years

lorenzoconforti — Wed, 25 Mar 2020 14:15:41 GMT

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] ) )

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 25 Mar 2020 15:06:14 GMT

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]) )

Re: Sum for the Latest Month across the Years

lorenzoconforti — Wed, 25 Mar 2020 15:08:06 GMT

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

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 25 Mar 2020 15:32:54 GMT

Please find attached the dashboard i am working with

Re: Sum for the Latest Month across the Years

lorenzoconforti — Wed, 25 Mar 2020 16:24:40 GMT

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

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 25 Mar 2020 16:57:46 GMT

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]))

Re: Sum for the Latest Month across the Years

lorenzoconforti — Wed, 25 Mar 2020 17:02:21 GMT

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

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 25 Mar 2020 17:12:41 GMT

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

Re: Sum for the Latest Month across the Years

lorenzoconforti — Wed, 25 Mar 2020 17:13:35 GMT

can you re-share the file?

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 25 Mar 2020 17:32:29 GMT

please find it attached

Re: Sum for the Latest Month across the Years

lorenzoconforti — Wed, 25 Mar 2020 18:06:56 GMT

The calculation works as expected (I've added a table that shows this)

The issue is that you are using a pivot table; there are different behaviours when expanded/collapsed . You need to use two different formulas and pick using the Dimensionality() function

See attached

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 25 Mar 2020 18:33:51 GMT

Yes it works for this specific pivot table and chart combination.

But i guess there isn't a way to make a true Master Measure that can be applied regardless of chart composition.

Also one that allows u to show totals, as currently in pivot table totals stay null.

Re: Sum for the Latest Month across the Years

lorenzoconforti — Wed, 25 Mar 2020 18:47:19 GMT

Totals are just another level of dimensionality (0)

See attached

Re: Sum for the Latest Month across the Years

ghaliabed — Thu, 02 Apr 2020 12:27:34 GMT

Yes this works for specific charts

but yes so far haven't found a way to make it a master measure. Available for self-service

only as prepackaged calculations

Re: Sum for the Latest Month across the Years

lorenzoconforti — Thu, 02 Apr 2020 13:19:03 GMT

Correct. I can't see a way of making it a master measure

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 22 Apr 2020 12:31:18 GMT

Hello

I am trying a new approach now and wanted to see if can help with the last part.

Assumptions:

Have Pivot table with:

Year --> Quarter --> Month in the columns
Any dimension in the Rows

Current measure being used:

if ( Dimensionality() = 2 ,sum(SignedData) // Month Level ,sum({<[Fiscal Month Number]={$(=max([Fiscal Month Number]))}>}SignedData) )

If i make the pivot table to only have Quarter --> Month OR Year --> Month

It is giving me the results i want

Ex: (of course switch Dimensionality()=1)

Quarter --> Month

Looking at Q.3 when expanded , the values at the Month Level for the Rows is correct but the Total is wrong
Looking at Q.4 when not expanded, the values at the Rows is correct and the Total is correct

This is closer to what is needed as i can switch the Dimension present in the row and not be affected.

But as you can the roll up is still an issue for Totala, and also when i use all three Time levels (Year --> Q --> Month)

I get results like ones below:

Where only the Quarter and the Month that is at the max has values while the rest become ZERO.

Re: Sum for the Latest Month across the Years

lorenzoconforti — Wed, 22 Apr 2020 13:02:29 GMT

can you upload the dashboard?

Also, totals only work for Q4 because that's when you have your max([Fiscal Month Number]); when you use set analysis, you are looking for the max month across the current selection, not per row/column. You will need to use firstsortedvalue and aggr like we've done before

Re: Sum for the Latest Month across the Years

ghaliabed — Wed, 22 Apr 2020 13:30:08 GMT

Please find dashboard here.

And yes i get that for the Max(Fiscal Month Number)

But isn't a set analysis where the value of the Max changes by the grouping at the chart level ?

Since max(fiscal Month Number) at Q1 level is different then Q2, but seems value in the set analysis only gets calculated before the dashboard is rendered.