1 Reply Latest reply: Apr 26, 2018 1:45 PM by Sunny Talwar

# QlikSense AGGR Function

Dear experts,

I’m looking for an aggregate function in Qlik sense to do the below

My data table looks like below.

 Part Plan Actual Percentage(Caluclated as Actual/ Plan) A 3 3 100% B 3 6 200% C 4 4 100% D 5 0 0% E 0 2 #DIV/0! F 0 0 #DIV/0! G 4 3 75% H 2 0 0% I 0 3 #DIV/0!

I want the output as a bar graph with the data as shown below

 Percentage Count 0% 2 75% 1 100% 2 200% 1 >100% 2 N/A 1

Please see the colors from the input table and out table for a clear understanding of the data.

Please note that when the plan is 0 and actual is >0, I want the Percentage to show as > 100% with count as number of records (in this case it is 2).

If both plan and actual are 0. I want the Percentage to be N/A or something unique. So, it can be treated as no plan and no actual (In this case we have only 1 record).

• ###### Re: QlikSense AGGR Function

May be this

Dimension

Aggr(

If(Plan = 0 and Actual = 0, 'N/A',

If(Plan = 0, '>100%',

If(Actual = 0, '0%',

If(Actual/Plan <= 0.75, '75%',

If(Actual/Plan <= 1, '100%'

If(Actual/Plac <= 2, '200%'))))))

,Part)

Expression

Count(DISTINCT Part)