Set Analysis: percentage calculation on variables

[rm1]

Hello everyone.

I am new on Qlik Sense. I'm working on a dataset created by some former colleagues.

They created some variables for sales e.g. vSales_CY (sales of current year) and vSales_CY_product (sales of current year by product). The problem is that I don't know how they have created these variables (I don't know which items they had use to create the variables).

Now I need to calculate the percentage of a specific selection of products on total sales  1) Sum(vSales_CY_product)/Sum(TOTAL vSales_CY_product)  or

2) Sum({$}vSales_CY_product)/Sum(TOTAL vSales_CY_product) or

3) $(vSales_CY_product) / (TOTAL vSales_CY_product) 

Previous formulas don't work because I can't perform operation on variables (and i am a newbie so they could be wrong). But I hope they can explain easily the calculation I need

My question is, is there any method to perform a percentage calculation using those variables? where am I doing wrong?

Thanks in advance

Mario

[Channa]

1) Sum(vSales_CY_product)/Aggr(Sum(vSales_CY_product),UrDim)

1) Sum(vSales_CY_product)/(Sum( total <dim>vSales_CY_product))

[Lisa_P]

 If you have editing capabilities you may be able to see the expression used to create the variable by editing any sheet and going to the variables bottom left of the assets panel

[rm1]

Many thanks for the hints. I found the box and i checked the variable code. I obtain the following string:

sum({<year ={'$(=max(year))'}, [date] = {"<=$(=$(vMaxDate))"},CustomerFacing_Flag={1}, [currency to]=>} [quantity product])     -->    vSales_CY_product

Now, if I should calculate the percentage of my current selection (es. % sales of product 1 on total sales) how can i do?

sum({<year ={'$(=max(year))'}, [date] = {"<=$(=$(vMaxDate))"},CustomerFacing_Flag={1}, [currency to]=>} [quantity product])  /  sum({<year ={'$(=max(year))'}, [date] = {"<=$(=$(vMaxDate))"},CustomerFacing_Flag={1}, [currency to]=>} [quantity product])    -->   obviously gives me 100% because I didn't considered the current product selection on numerator or the total product sales on denominator.

Thank you very much

[rm1]

I specify that if I try to use a formula like the following one, and I select 1 or 2 products in my product list to check their sales percentage respect to the total sales, i still obtain 100 percent.

sum({$}[quantity product]) / sum(total [quantity product])

I don't understand if the formula doesn't maintain the selection, or if the system doesn't recognize the command "total" as the total quantity without filters.

Thanks in advance for any help

[Lisa_P · Accepted solution]

Hi RM,

The issue with this expression is that you are taking selections into account. What you need is to take into account selections on the numerator, but not the denominator, so:

sum([quantity product]) / sum({1}total [quantity product])

the {1} ignores selections

[rm1]

Thanks a lot Lisa_P!!

your hint has been very helpful. I used the following formula and it worked. I couldn't use the denominator sum({1}total [quantity product]) suggested because it ignores too many base filters (e.g. months, Business Unit, etc). Formula became:

sum({$<Product={'Products name'}, year={2019}>}[quantity product]) / sum({<year={2019}>} total [quantity product])

The last thing that I would like to correct is the set modifier <Product>. Imagine 3 BU with 2 department each, and some of the products are sold by both dept.. I need to analyze only some products maintaining the starting filters (that's why I can't use the {1} on denominator total quantity). But if I select 3 or 4 products from a graph, the system recalculates the total quantity on those 4 products. So I obtain a percentage of 100%.

Using the formula in bold I am forced to create as many formulas as the number of selections that I need (4 lists of products --> 4 formulas with different set modifiers <Products>). It's very inefficient.

I should create an interactive dashboard where, if I select only some products from a chart, I can maintain my total [quantity product] in the denominator based on starting total panel and not on the total quantity product of new selection.

 Is there a way to make it easier?

[rm1 · Accepted solution]

Solved applying:

sum({$<year={2019}>} [quantity product]) / sum({<year={2019}, Product=>} total [quantity product])

Thanks to everyone for the help