I have an excel file with 400 values and I only want to display the values that are 25% or less of the total average value on a Qlik Sense chart / table.using an if statement
For example, if I have an total average value of 100 I want to display all values of 25 and less.
Thanks in advance
Solved! Go to Solution.
I got the answer I needed as follows and unchecking the include null values tab
aggr(if (avg(Sales)/avg(TOTAL Sales) <0.25, Sales),Sales)
thanks for your help
you can try to create calculated dimension like below,
for example, you have Sales and Product.
=aggr(if (sum(Sales)/sum(TOTAL Sales) <0.25, Product),Product)
(u can just apply above expression in qliksense as well and it should work) // best way try to apply this in set analysis expression
thanks for your response
I tried the formula using Qlik sense and it added a column with the Product names If I try the following
=aggr(if (sum(Sales)/sum(TOTAL Sales) <0.25, Sales),Sales)
i get a column with the Sales values. and the table is not limited to the products of 25% or below.
Maybe you should try by adding another parentesis:
aggr(if ((sum(Sales)/sum(TOTAL Sales)) =<0.25, Sales),Sales)
have your tried like this?
simply create any bar/table/pivot table chart based on your requirement
add below Calculated dimension and measure
=aggr(if ( (sum(Sales)/sum(TOTAL Sales)) =<0.25, Product),Product)
note: Make sure to Suppress null value in this calculated dimension.
Sales Contribution %
I'm getting a new column with no values, but no errors detected in the formula and when I uncheck the include null values box all rows disappear. I changed the position of the = sign to <= but get a value that makes no sense and shows a value that is approx 11% of the sales value!!!
Any other suggestion?
Can you share the mock data and expected out in excel format. i've tested my end it's working.
I can't attach an excel file but extract below should help
Basically what I want to show on the table is only the products where the % of average sales is 25% or below. Using your expression the column shows the "-" symbol instead of a value and shows all 400 rows regardless of the % of average sales
thanks again for your help !
|Product||Sales||% of sales||% of average sales|
|Multi Game Poker||$5,548.63||0.01%||5.16%|
|Multi Game Poker||$6,427.97||0.01%||5.97%|
|Multi Game Poker||$6,972.38||0.02%||6.48%|
|Multi Game Poker||$7,200.70||0.02%||6.69%|
|I love Lucy Channel Surfing||$7,465.03||0.02%||6.94%|
|Multi Game Poker||$7,820.67||0.02%||7.27%|
|Multi Game Poker||$7,923.34||0.02%||7.36%|
|Multi Game Poker||$8,010.28||0.02%||7.44%|
|Gorilla Chief II||$8,227.45||0.02%||7.64%|
|American Roulette Deluxe||$8,353.71||0.02%||7.76%|
|Multi Game Poker||$9,837.68||0.02%||9.14%|