Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

adding values below 25% of the total average to a chart using an if statement

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi All


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

View solution in original post

10 Replies
devarasu07
Master II
Master II

Hi,

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

Capture.JPG

Anonymous
Not applicable
Author

Hi Devarasu,

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.

Any suggestions?

Thiago_Justen_

Maybe you should try by adding another parentesis:

aggr(if ((sum(Sales)/sum(TOTAL Sales)) =<0.25, Sales),Sales)

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
devarasu07
Master II
Master II

Hi,

have your tried like this?

Steps,

simply create any bar/table/pivot table chart based on your requirement

add below Calculated dimension and measure

Dimension:

=aggr(if ( (sum(Sales)/sum(TOTAL Sales)) =<0.25, Product),Product)

note: Make sure to Suppress null value in this calculated dimension.

Measure:

Sales:

Sum (Sales)

Sales Contribution %

Sum(Sales)/sum(Total Sales)

Thanks, Deva

Anonymous
Not applicable
Author

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?



devarasu07
Master II
Master II

Hi,

Can you share the mock data and expected out in excel format. i've tested my end it's working.

Anonymous
Not applicable
Author

Hi Devarasu,

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 !

    

ProductSales% of sales% of average sales
Multi Game Poker$5,548.630.01%5.16%
Multi Game Poker$6,427.970.01%5.97%
Multi Game Poker$6,972.380.02%6.48%
Multi Game Poker$7,200.700.02%6.69%
I love Lucy Channel Surfing$7,465.030.02%6.94%
Multi Game Poker$7,820.670.02%7.27%
Multi Game Poker$7,923.340.02%7.36%
Multi Game Poker$8,010.280.02%7.44%
Gorilla Chief II$8,227.450.02%7.64%
American Roulette Deluxe$8,353.710.02%7.76%
Royal Derby$9,118.350.02%8.47%
Royal Derby$9,209.200.02%8.56%
Multi Game Poker$9,837.680.02%9.14%
Anonymous
Not applicable
Author

I just added an Excel file to the original post with all 400 rows

arvind1494
Specialist
Specialist

aggr(if (sum(Sales)/sum(TOTAL Sales) <0.25, Sales),Sales)