Job Count for previous FYYear in Pivot Table

[akelleway]

Hi All,

I have this Pivot Table, where the dimensions are 'Region' and 'FYYear', which is a Financial Year field from a Master Calendar. The table currently has one measure - 'Jobs', which is just a simple job count. Count(distinct RegionJobNumber)

But I am struggling with getting the same measure working for the previous FYYear. I am currently sat on this expression: Count({$<FYYear={"$(=max(FYYear)-1)"}>}distinct RegionJobNumber) but as you can see from the attached image, it is not working. The data itself goes back to 2015, but the FYYear dimension is filtered to >= 2022, so every column has the data required for this to work.

For now I am just trying to get this working as a standalone column measure, to check that the correct output is being produced. But eventually this will go into another measure, which will compare the Job Count (FYYear) to Job Count (FYYear-1) and then aggregate them by another field [CUSTOMER GROUP] to return the number of Customer Groups who had fewer jobs than the previous FYYear.

Any suggestions would be much appreciated.

[MatheusC]

@akelleway 

Use the before function

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterReco...


- Matheus

[kim186flora]

Hello,

Use this measure for previous FYYear job count:Count({<FYYear = {"$(=Only(FYYear) - 1)"}>} DISTINCT RegionJobNumber)
It calculates the distinct job count for the prior FYYear relative to the current row in your pivot.

Best Regard,

Kim

CostcoBusinessCenter

[Qrishna]

Pease provide some some data.

[akelleway]

Hi Matheus,

Thank you for your reply.

I had come across the before function previously, but was under the impression that this would not work in the further expression which I am ultimately looking to create as mentioned above. (Inside a sum/aggr)

I am also not sure how I would get this to work for the first column in the pivot. As whilst the underlying data is there, the first column for FY22 is returning nulls as the FY21 is filtered out and not included in the table.

Many Thanks,

Ashley