Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to know a product family's share of sales of the total sales. I want to plot this over a number of years in a pivot table. The function I use is sum(sales)/sum(TOTAL sales). See attached a simplified example.
However, this calculates the total of all years together and not the total of the year which is displayed in the pivot table column. How can I realise this?
NOTE: the datamodel contains 10 years of data. With use of a filter the user can choose whether to display 1,2,3 years etc. So in the example attached a filter is placed for the years 2023,2022, 2021.
Many thanks!
Hi @Giel0
Can you try using:
sum(sales)/sum(TOTAL <Year> sales)
This will make that your "Year" Field, will be considerated on the calculation.
-Zapparoli
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics
You need to add a set analysis which ignored certain or maybe all selections, for example:
sum(sales)/sum({< Productfamily > } TOTAL <Year> sales)
sum(sales)/sum({1} TOTAL <Year> sales)
Hi @Giel0
Can you try using:
sum(sales)/sum(TOTAL <Year> sales)
This will make that your "Year" Field, will be considerated on the calculation.
-Zapparoli
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics
Hi @Zapparoli
Thanks, that works!
However, the calculation is correct when no filters are being placed on a product family. When I filter by a specific product family to do an analysis, the total count is also filtered as 100%. How can I extend the set analysis so that the filter on "Product family" is excluded and the 'total sales' stays the same?
You need to add a set analysis which ignored certain or maybe all selections, for example:
sum(sales)/sum({< Productfamily > } TOTAL <Year> sales)
sum(sales)/sum({1} TOTAL <Year> sales)
@marcus_sommer thanks, that's exactly what I was looking for!