Calculate the percentage of sales for the year in a pivot table.

[SBDataspark]

Hi there fellow Qlik enthousiasts!

I'm trying to calculate the percentage of sales per year in a pivot table.

This pivot table is a simplification to make it easy to understand my question.
It shows quantity sold per item per customer or if you switch the two quantity sold per customer per item.

Now I'd like to calculate the percentage quantity sold compared to the total quantity sold for every year.

I'm trying all sorts of variants of this expression:

=Sum([Transactions.Quantity])
/
Sum(total [Transactions.Quantity])

But I can't get this to work.
In the screenshot I've added the correct percentage by hand.
So item "Ball" is 28,9% instead of 14% for the year 2021 instead of 14%. That's the percentage for all the years.

When I select a year it obviously shows the right percentage, because the dataset is limited to one year.

What do I need to change to my expression to make this work correctly regardless of the order of dimensions and the selection made on the table?
Thanks!

Kind regards,

Sebb.

[tincholiver]

did u try using aggr?

like this Agrr(sum(Sum([Transactions.Quantity]),dimension_1,dimension2)

[tincholiver]

sorry is

Agrr(Sum([Transactions.Quantity]),dimension_1,dimension2)

[SBDataspark · Accepted solution]

Hi @tincholiver,

Thank you for your efforts in trying to help me with this question.
I managed to find the answer.
This is de expression that I created to make it work:

=Sum([Transactions.Quantity])
/
Sum(total <[TransactionDate.autoCalendar.Year]> [Transactions.Quantity])

Kind regards,

Sebb.