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

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

Anonymous — Sat, 27 Jan 2018 04:26:28 GMT

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

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

devarasu07 — Sat, 27 Jan 2018 09:38:37 GMT

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

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

Anonymous — Sat, 27 Jan 2018 17:49:04 GMT

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?

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

Thiago_Justen_ — Sat, 27 Jan 2018 17:54:33 GMT

Maybe you should try by adding another parentesis:

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

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

devarasu07 — Sat, 27 Jan 2018 21:40:18 GMT

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

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

Anonymous — Sun, 28 Jan 2018 06:09:21 GMT

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?

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

devarasu07 — Sun, 28 Jan 2018 07:29:18 GMT

Hi,

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

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

Anonymous — Sun, 28 Jan 2018 17:41:46 GMT

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 !

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%
Royal Derby	$9,118.35	0.02%	8.47%
Royal Derby	$9,209.20	0.02%	8.56%
Multi Game Poker	$9,837.68	0.02%	9.14%

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

Anonymous — Sun, 28 Jan 2018 22:57:30 GMT

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

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

arvind1494 — Mon, 29 Jan 2018 07:06:59 GMT

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

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

Anonymous — Mon, 29 Jan 2018 18:26:45 GMT

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