topic Percentage Year on Year change in Insight Advisor

Percentage Year on Year change

tripod6999 — Mon, 03 Mar 2025 12:16:32 GMT

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

Re: Percentage Year on Year change

JandreKillianRIC — Wed, 05 Mar 2025 08:18:16 GMT

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

Re: Percentage Year on Year change

diegozecchini — Wed, 05 Mar 2025 08:43:24 GMT

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

Re: Percentage Year on Year change

tripod6999 — Wed, 05 Mar 2025 08:59:07 GMT

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.

Re: Percentage Year on Year change

tripod6999 — Wed, 05 Mar 2025 09:06:18 GMT

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.

Re: Percentage Year on Year change

diegozecchini — Wed, 05 Mar 2025 10:30:50 GMT

ops, sorry for mistake. Glad to be helpful.

Re: Percentage Year on Year change

tripod6999 — Wed, 05 Mar 2025 16:17:17 GMT

Diego,

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

Francis

Re: Percentage Year on Year change

tripod6999 — Wed, 05 Mar 2025 15:59:30 GMT

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