Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

andyscslots
New Contributor III

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
andyscslots
New Contributor III

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

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

10 Replies
devarasu07
Honored Contributor II

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

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

andyscslots
New Contributor III

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

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
Valued Contributor III

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

Maybe you should try by adding another parentesis:

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

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
devarasu07
Honored Contributor II

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

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

andyscslots
New Contributor III

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

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
Honored Contributor II

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

Hi,

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

andyscslots
New Contributor III

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

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%
andyscslots
New Contributor III

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

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

arvind1494
Valued Contributor

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

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