Percentage Year on Year change

[tripod6999]

I am trying to adapt the following Qlik Sense expression which is supposed to calculate the percentage change for each value in the field 'Gender' (Female, Male, Prefer not to say) from the previous year (the year is in the field 'Year' and has the values 2020, 2021, 2022, 2023 and 2024 - I would like the percentage change in the year 2020 to be NA or a dash as this is the first year and does not have any years before it.

At the moment the expression is giving the percentage change between the current row and the one above it and not giving any values for 'Females' at all (see attached picture of output). How do I need to adapt the expression so that it works correctly as shown in the final column? I have tried using AGGR with 'Year' and 'Gender' but cannot get it to work. Any help will be greatly appreciated:

IF(
Count(DISTINCT UniqueID) >= 5 OR Count(DISTINCT UniqueID) = 0,

IF(
Above(Count(DISTINCT UniqueID)) > 0,
Num(
(Count(DISTINCT UniqueID) - Above(Count(DISTINCT UniqueID))) /
Above(Count(DISTINCT UniqueID)),
'0.0%'
),
'N/A'
),

'<5'
)

[JandreKillianRIC]

Hi @tripod6999 

If you always have 4 values in the the gender field - To get the "correct" previous (above) values use 

Above(Total Sum(Number), 4)

For the YoY change 

Aggr((Sum(Number) - Above(Sum(Number)))/Above(Sum(Number)), Gender, (Year, (NUMERIC, ASCENDING)))

Regards Jandre

[diegozecchini]

Hi!
try
IF(
  Count(DISTINCT UniqueID) >= 5 OR Count(DISTINCT UniqueID) = 0,
  IF(
       Above(TOTAL Count(DISTINCT UniqueID), 1, Year) > 0,
       Num(
          (Count(DISTINCT UniqueID) - Above(TOTAL Count(DISTINCT UniqueID), 1, Year)) /
           Above(TOTAL Count(DISTINCT UniqueID), 1, Year),
           '0.0%'
       ),
       'N/A'
   ),
    '<5'
)

2020 values should be "N/A" and from 2021 onwards, each gender category will have a proper year-on-year percentage change based on the previous year's count

[tripod6999 · Accepted solution]

Hello Diego,
Thank you for the reply. That expression seems to work with a very minor change. Instead of the '1' (offset?) in the second part of the expression, I needed to change it to '4'  as shown in bold underline below i.e. the expression is:

IF(
  Count(DISTINCT UniqueID) >= 5 OR Count(DISTINCT UniqueID) = 0,
  IF(
       Above(TOTAL Count(DISTINCT UniqueID), 4, Year) > 0,
       Num(
          (Count(DISTINCT UniqueID) - Above(TOTAL Count(DISTINCT UniqueID), 4, Year)) /
           Above(TOTAL Count(DISTINCT UniqueID), 4, Year),
           '0.0%'
       ),
       'N/A'
   ),
    '<5'
)

Thank you so much for your help in sorting this out. It really was proving to be a real pain.

[tripod6999 · Accepted solution]

Jandre,

Thank you for your response. That expression seems to work. For my purposes the expression becomes:

NUM(Aggr((Count(DISTINCT UniqueID) - Above(Count(DISTINCT UniqueID)))/Above(Count(DISTINCT UniqueID)), Gender, (Year, (NUMERIC, ASCENDING))),'0.0%')

Thanks again for your response. It is very much appreciated.

[diegozecchini]

ops, sorry for mistake. Glad to be helpful.

[tripod6999]

Diego,

No worries and it did help me to solve the problem so thank you!

Francis

[tripod6999]

Hello Diego,

A problem that I have encountered with the expression is when I filter on a dimension and the number of values within each year in the field 'Gender' aren't the same, it gives the incorrect values. Is there any way of ensuring that the expression matches on the values in the Gender field and if not gives an N/A again? i.e. if there aren't any 'Other' values in a previous year, it does not match to 'Prefer not to say' as that would be the row that is selected. In other words, the 'Offset' value of 4 might vary. I hope that makes sense.

Francis